The tutorial shows how to separate first and last name in Excel with formulas or Text to Columns, and how to quickly split a column of names in various formats to first, last and middle name, salutations and suffixes.
It is a very common situation in Excel that your worksheet contains a column of full names, and you want to split first and last name into separate columns. The task can be accomplished in a few different ways - by using the Text to Columns feature, formulas, and Split Names tool. Below you will find full details on each technique.
How to split names in Excel with Text to Columns
In situations when you have a column of names of the same pattern, for example only first and last name, or first, middle and last name, the easiest way to split them into separate columns is this:
- Select the column of full names that you'd like to separate.
- Head to the Data tab > Data Tools group and click Text to Columns.
- On the first step of the Convert Text to Columns Wizard, select the Delimited option and click Next.
- On the next step, select one or more delimiters and click Next.
In our case, different parts of names are separated with spaces, so we choose this delimiter. The Data preview section shows that all of our names are parsed just fine.
Tip. If you are dealing with names separated with a comma and space like Anderson, Ronnie, then check the Comma and Space boxes under Delimiters, and select the Treat consecutive delimiters as one checkbox (usually selected by default). - On the last step, you select the data format and destination, and click Finish.
The default General format works nice in most cases. As the Destination, specify the topmost cell in the column where you want to output the results (please keep in mind that this will overwrite any existing data, so be sure to choose an empty column).
Done! The first, middle, and last name are divided into separate columns:
Separate first and last name in Excel with formulas
As you have just seen, the Text to Columns feature is quick and easy. However, if you plan to make any changes to the original names and are looking for a dynamic solution that will update automatically, you'd better divide names with formulas.
How to split first and last name from full name with space
These formulas cover the most typical scenario when you have the first name and last name in one column separated by a single space character.
Formula to get first name
The first name can be easily extracted with this generic formula:
You use the SEARCH or FIND function to get the position of the space character (" ") in a cell, from which you subtract 1 to exclude the space itself. This number is supplied to the LEFT function as the number of characters to be extracted, starting on the left side of the string.
Formula to get last name
The generic formula to extract a surname is this:
In this formula, you also use 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 extract that many characters from the right side of the string.
With the full name in cell A2, the formulas go as follows:
Get the first name:
=LEFT(A2,SEARCH(" ",A2)-1)
Get the last name:
=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))
You enter the formulas in cells B2 and C2, respectively, and drag the fill handle to copy the formulas down the columns. The result will look something similar to this:
If some of the original names contain a middle name or middle initial, you'd need a bit more tricky formula to extract the last name:
=RIGHT(A2, LEN(A2) - SEARCH("#", SUBSTITUTE(A2," ", "#", LEN(A2) - LEN(SUBSTITUTE(A2, " ", "")))))
Here is a high-level explanation of the formula's logic: you replace the last space in the name with a hash sign (#) or any other character that do not appear in any name and work out the position of that char. After that, you subtract the above number from the total string length to get the length of the last name, and have the RIGHT function extract that many characters.
So, here's how you can separate the first name and surname in Excel when some of the original names include a middle name:
How to separate first and last name from name with comma
If you have a column of names in the Last name, First name format, you can have them split into separate columns by using the following formulas.
Formula to extract first name
Like in the above example, you use the SEARCH function to determine the position of a space character, and then subtract it from the total string length to get the length of the first name. This number goes directly to the num_chars argument of the RIGHT function indicating how many characters to extract from the end of the string.
Formula to extract last name
To get a surname, you use the LEFT SEARCH combination discussed in the previous example with the difference that you subtract 2 instead of 1 to account for two extra characters, a comma and a space.
With the full name in cell A2, the formulas take the following shape:
Get the first name:
=RIGHT(A2, LEN(A2) - SEARCH(" ", A2))
Get the last name:
=LEFT(A2, SEARCH(" ", A2) - 2)
The below screenshot shows the results:
How to split full name to first, last, and middle name
Splitting names that include a middle name or middle initial requires slightly different approaches, depending on the name format.
If your names are in the First name Middle name Last name format, the below formulas will work a treat:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Full name | First name | Middle Name | Last name |
| 2 | FirstName MiddleName LastName | =LEFT(A2,SEARCH(" ", A2)-1) |
=MID(A2, SEARCH(" ", A2) + 1, 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 first name, you use the already familiar LEFT SEARCH formula.
To get the last name, determine the position of the 2nd space by using nested SEARCH functions, subtract the position from the total string length, and get the length of the last name as the result. Then, you supply the above number to the RIGHT function instructing it to pull that number of characters from the end of the string.
To extract the middle name, you need to know the position of both spaces in the name. To determine the position of the first space, use a simple SEARCH(" ",A2) function, to which you add 1 to start the extraction with the next character. This number goes to the start_num argument of the MID function. To work out the length of the middle name, you subtract the position of the 1st space from the position of the 2nd space, subtract 1 from the result to get rid of a trailing space, and put this number in the num_chars argument of MID, telling it how many characters to extract.
And here are the formulas to separate names of the Last name, First name Middle name type:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Full name | First name | Middle name | Last Name |
| 2 | LastName, FirstName MiddleName | =MID(A2, SEARCH(" ",A2) + 1, SEARCH(" ", A2, SEARCH(" ", A2) + 1) - SEARCH(" ", A2) -1) |
=RIGHT(A2, LEN(A2) - SEARCH(" ", A2, SEARCH(" ", A2, 1)+1)) |
=LEFT(A2, SEARCH(" ",A2,1)-2) |
| Result: | White, David Mark | David | Mark | White |
A similar approach can be used to split names with suffixes:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Full name | First name | Last name | Suffix |
| 2 | FirstName LastName, Suffix | =LEFT(A2, SEARCH(" ",A2)-1) |
=MID(A2, SEARCH(" ",A2) + 1, SEARCH(",",A2) - SEARCH(" ",A2)-1) |
=RIGHT(A2, LEN(A2) - SEARCH(" ", A2, SEARCH(" ",A2)+1)) |
| Result: | Robert Furlan, Jr. | Robert | Furlan | Jr. |
That's how you can split names in Excel by using different combinations of functions. To better understand and probably reverse-engineer the formulas, you are welcome to download our sample workbook to Separate Names in Excel.
Separate name in Excel 2013 and 2016 with Flash Fill
Everyone knows that Excel's Flash Fill can quickly fill data of a specific pattern. But did you know that it can also split data? Here's how:
- Add a new column next to the column with the original names and type the name part that you want to extract in the first cell (the first name in this example).
- Start typing the first name in the second cell. If Excel senses a pattern (in most cases it does), it will populate the first names in all other cells automatically.
- All you have to do now is to press the Enter key :)
Split Names tool - fastest way to separate names in Excel
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 you are dealing with different name formats, the above methods will mess up your worksheets by putting some name parts in wrong columns or returning errors, for example:
In such situations, you can commit the work to our Split Names tool, which perfectly recognizes multi-part names, over 80 salutations and about 30 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 cell containing a name you want to separate and click the Split Names icon on the Ablebits Data tab > Text group.
- Select the desired names parts (all of them in our case) at click Split.
Done! Different 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 fiddling with commas and spaces, no pain at all.
If you are curious to try the Split Names tool in your own worksheets, feel free to download a 14-day trial version of the Ultimate Suite for Excel. If the tool meets your expectations and you decide to get a license, don't miss the 15% off coupon code that we provide especially for our blog readers: AB14-BlogSpo
I thank you for reading and hope to see you on our blog next week!
Thanks svetlana, for your effort.
Excellent! Thanks...
how to you separate multiple names
SIR,
IF THE CONDITION LIKE THIS
CHIRANJIVI KUMAR SHAH
MAYANK KUMAR
RANJAN SINGH RAJPUT
GIVE ONE FORMULAE FOR FINDING MIDDLE NAME
Lalit:
I guess it depends on how many names you've got and the use you have for the middle name, but I would try first to use the Text-to-Columns approach. It's pretty fast and the end result is that each name is in a separate cell and you can do what you like with them at that point.
Select the names, click the Text-to-Columns button on the ribbon.
You'll see the data in that window and you should select the Delimited button click Next
Choose the Space button, click Next
Choose the General button click Finish.
The middle name is in its own separate cell waiting for you.
In the case of that second name that appears not to have a middle name, you'll have to cut and paste the "Kumar" part into the last column. You can do a LEFT and then a RIGHT LEN splitting formula, but I don't think that would be much quicker.
However, if the name list is being lengthened frequently , you might want to use another approach.
I found this technique on the web somewhere several years ago and it works great. So, kudos to whoever wrote it. Especially for the names which have no middle name or initial.
Keep in mind this box will not allow the total length of some of these formulas to be on one line, but they are to be entered in your formula bar on one line. So, be careful how you copy and paste these.
Where the complete name is in H44 enter this into the cell that will hold the first name:
=IFERROR(LEFT(H44,FIND(" ",SUBSTITUTE(H44,","," "))-1),"")
Next, in a cell where you want to hold the middle name enter: =IFERROR(MID(TRIM(SUBSTITUTE(H44,","," ")),FIND(" ",TRIM(SUBSTITUTE(H44,","," ")))+1,FIND(" ",MID(TRIM(SUBSTITUTE(H44,","," ")),FIND(" ",TRIM(SUBSTITUTE(H44,","," ")))+1,255))-1),"")
Note that this formula works great even if there is no middle name. In that case it will return a blank.
Lastly, in the cell that you want to hold the last name enter:
=TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(H44,","," "))," ",REPT(" ",100)),100))
If your data is in more rows than 100, replace the 100's with the last row number.
Copy these down the three columns and you're the office hero - at least for the day.
So, there you have it, two ways to split text in Excel.
Which formula I need to use when the name is like below
Shiraj Ahmed Allabaksh Salagar
Shiraj Ahmed Allabaksh Jallalludin Salagar