How To Pull Last Name In Excel

The tutorial shows how to separate outset and last name in Excel with formulas or Text to Columns, and how to quickly split a column of names in diverse formats to commencement, last and middle proper name, salutations and suffixes.

It is a very common situation in Excel that your worksheet contains a cavalcade of total names, and you want to split first and last name into separate columns. The task can exist accomplished in a few different ways - by using the Text to Columns feature, formulas, and Split up Names tool. Below you will find full details on each technique.

How to split full names with Text to Columns feature
How to carve up names in Excel with formulas
- Split starting time and concluding proper noun from total name with space
- Separate first and last name from proper noun with comma
- Split first, last and middle proper noun
Separate name in Excel 2013, 2016 and 2019 with Flash Fill
Split Names tool - fastest way to carve up names in Excel

How to divide names in Excel with Text to Columns

In situations when you have a column of names of the same pattern, for example merely first and last name, or kickoff, eye and terminal proper noun, the easiest mode to split them into carve up columns is this:

Select the column of full names that you'd like to separate.
Caput to the Information tab > Data Tools group and click Text to Columns.
On the start stride of the Convert Text to Columns Wizard, select the Delimited option and click Adjacent.
On the next step, select one or more delimiters and click Next.
In our case, different parts of names are separated with spaces, and then we choose this delimiter. The Data preview department shows that all of our names are parsed but fine.

Tip. If you are dealing with names separated with a comma and space similar Anderson, Ronnie, then bank check the Comma and Space boxes under Delimiters, and select the Treat sequent delimiters as 1 checkbox (usually selected by default).
On the last step, you select the data format and destination, and click Finish.
The default General format works dainty in most cases. As the Destination, specify the topmost cell in the column where yous want to output the results (please go along in listen that this volition overwrite any existing data, so exist sure to choose an empty column).

Done! The first, middle, and last name are divided into split up columns:

Separate first and terminal proper noun in Excel with formulas

Equally you take just seen, the Text to Columns feature is quick and easy. However, if yous plan to make any changes to the original names and are looking for a dynamic solution that volition update automatically, y'all'd amend split up names with formulas.

How to split beginning and last name from full proper noun with infinite

These formulas comprehend the almost typical scenario when you have the get-go name and terminal name in ane cavalcade separated by a single space character.

Formula to get start name

The kickoff name can be easily extracted with this generic formula:

LEFT(cell, SEARCH(" ", jail cell) - 1)

You use the SEARCH or FIND function to get the position of the space grapheme (" ") in a jail cell, from which yous subtract ane to exclude the space itself. This number is supplied to the LEFT function equally the number of characters to be extracted, starting on the left side of the string.

Formula to get last proper name

The generic formula to extract a surname is this:

RIGHT(cell, LEN(cell) - SEARCH(" ", jail cell))

In this formula, you likewise apply the SEARCH function to find the position of the space char, subtract that number from the total length of the string (returned by LEN), and get the RIGHT function to excerpt that many characters from the right side of the string.

With the total name in cell A2, the formulas go every bit follows:

Get the offset proper name:

=LEFT(A2,SEARCH(" ",A2)-i)

Become the last name:

=Correct(A2,LEN(A2)-SEARCH(" ",A2,1))

You enter the formulas in cells B2 and C2, respectively, and elevate the fill handle to re-create the formulas down the columns. The consequence will look something similar to this:
Formulas to split first and last name in Excel

If some of the original names contain a eye name or middle initial, you'd need a bit more than catchy formula to extract the last name:

=Right(A2, LEN(A2) - SEARCH("#", SUBSTITUTE(A2," ", "#", LEN(A2) - LEN(SUBSTITUTE(A2, " ", "")))))

Here is a loftier-level caption of the formula's logic: you replace the final space in the name with a hash sign (#) or any other character that practice not appear in whatever name and piece of work out the position of that char. After that, y'all subtract the in a higher place number from the full string length to get the length of the last name, and take the Correct function extract that many characters.

So, here's how you can split the showtime name and surname in Excel when some of the original names include a middle name:
Formula to extract a surname when some of the original names include a middle name

How to separate first and last name from name with comma

If yous have a cavalcade of names in the Final name, First name format, y'all can have them split up into separate columns by using the following formulas.

Formula to extract kickoff proper noun

RIGHT(cell, LEN(cell) - SEARCH(" ", cell))

Like in the above example, you utilize the SEARCH function to determine the position of a space character, and so subtract it from the total string length to get the length of the first proper name. This number goes directly to the num_chars statement of the Right office indicating how many characters to extract from the end of the string.

Formula to extract terminal name

LEFT(cell, SEARCH(" ", cell) - 2)

To go a surname, yous employ the LEFT SEARCH combination discussed in the previous instance with the difference that you subtract 2 instead of 1 to account for 2 extra characters, a comma and a space.

With the full proper name in prison cell A2, the formulas take the post-obit shape:

Become the get-go proper name:

=RIGHT(A2, LEN(A2) - SEARCH(" ", A2))

Get the last name:

=LEFT(A2, SEARCH(" ", A2) - 2)

The beneath screenshot shows the results:
Separating first and last name from full name with comma

How to split total name to first, concluding, and middle name

Splitting names that include a centre name or middle initial requires slightly different approaches, depending on the name format.

If your names are in the First name Middle name Final name format, the below formulas volition piece of work a treat:

	A	B	C	D
1	Full name	Starting time name	Middle Proper name	Last name
2	FirstName MiddleName LastName	`=LEFT(A2,SEARCH(" ", A2)-one)`	`=MID(A2, SEARCH(" ", A2) + i, SEARCH(" ", A2, SEARCH(" ", A2)+1) - SEARCH(" ", A2)-1)`	`=Right(A2,LEN(A2) - SEARCH(" ", A2, SEARCH(" ", A2,1)+1))`
Result:	David Mark White	David	Mark	White

To get the offset name, y'all use the already familiar LEFT SEARCH formula.

To get the terminal proper name, determine the position of the ii^nd space by using nested SEARCH functions, subtract the position from the total string length, and get the length of the final proper noun every bit the upshot. Then, you supply the in a higher place number to the Correct office instructing it to pull that number of characters from the end of the string.

To extract the middle name, yous need to know the position of both spaces in the name. To determine the position of the outset space, use a simple SEARCH(" ",A2) function, to which yous add i to first the extraction with the next graphic symbol. This number goes to the start_num argument of the MID function. To piece of work out the length of the middle proper name, you subtract the position of the 1st space from the position of the second space, subtract 1 from the upshot to get rid of a trailing infinite, and put this number in the num_chars argument of MID, telling it how many characters to extract.
Splitting full name to first, last, and middle name

And here are the formulas to split up names of the Terminal name, Start name Middle name type:

	A	B	C	D
1	Full name	First name	Middle name	Concluding Proper noun
2	LastName, FirstName MiddleName	`=MID(A2, SEARCH(" ",A2) + 1, SEARCH(" ", A2, SEARCH(" ", A2) + one) - SEARCH(" ", A2) -1)`	`=Correct(A2, LEN(A2) - SEARCH(" ", A2, SEARCH(" ", A2, one)+one))`	`=LEFT(A2, SEARCH(" ",A2,1)-2)`
Effect:	White, David Mark	David	Mark	White

A similar approach tin be used to dissever names with suffixes:

	A	B	C	D
one	Total name	First proper name	Concluding name	Suffix
ii	FirstName LastName, Suffix	`=LEFT(A2, SEARCH(" ",A2)-i)`	`=MID(A2, SEARCH(" ",A2) + one, SEARCH(",",A2) - SEARCH(" ",A2)-ane)`	`=Right(A2, LEN(A2) - SEARCH(" ", A2, SEARCH(" ",A2)+1))`
Result:	Robert Furlan, Jr.	Robert	Furlan	Jr.

That'south how you lot can split names in Excel by using different combinations of functions. To better sympathise and probably reverse-engineer the formulas, y'all are welcome to download our sample workbook to Divide Names in Excel.

Separate name in Excel 2013, 2016 and 2019 with Flash Fill

Everyone knows that Excel'due south Wink Fill can quickly fill data of a specific pattern. But did you know that it can likewise split data? Hither's how:

Add a new column side by side to the column with the original names and blazon the proper name function that you want to extract in the start jail cell (the first name in this example).
Start typing the first proper name in the second jail cell. If Excel senses a pattern (in most cases information technology does), it will populate the beginning names in all other cells automatically.
All y'all accept to practice now is to press the Enter fundamental :)

Tip. Commonly the Flash Fill feature is enabled by default. If it does not work in your Excel, click the Flash Fill button on the Data tab > Information tools grouping. If it nonetheless doesn't work, then go to File > Options, click Advanced, and brand sure the Automatically Flash Make full box is selected under Editing options.

Plain or tricky, Text to Columns, Flash Fill and formulas work well only for homogeneous datasets where all names are of the same type. If y'all are dealing with different proper noun formats, the higher up methods volition mess upwards your worksheets by putting some name parts in incorrect columns or returning errors, for instance:
Errors when splitting names in Excel

In such situations, you tin can commit the work to our Split Names tool, which perfectly recognizes multi-part names, over 80 salutations and virtually thirty different suffixes, and works smoothly on all version of Excel 2016 to Excel 2007.

With our Ultimate Suite installed in your Excel, a column of names in various formats can be split in 2 easy steps:

Select any prison cell containing a proper noun you desire to split up and click the Carve up Names icon on the Ablebits Data tab > Text group.
Select the desired names parts (all of them in our example) at click Split.

Done! Dissimilar parts of names are spread out across several columns exactly as they should, and the column headers are added automatically for your convenience. No formulas, no piddling with commas and spaces, no pain at all.
Different parts of names are spread out across several columns.

If you lot are curious to try the Separate Names tool in your own worksheets, feel free to download an evaluation version of the Ultimate Suite for Excel.