Split The First And Last Name With A Formula
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:
' first name
LEFT(cell, SEARCH(" ", cell) - 1)
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:
' last name
RIGHT(cell, LEN(cell) - SEARCH(" ", cell))
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 take the following shape:
' first name
=RIGHT(A2, LEN(A2) - SEARCH(" ", A2))
' last name
=LEFT(A2, SEARCH(" ", A2) - 2)
~ | A | B | C |
---|---|---|---|
1 | Full Name | First Name | Last Name |
2 | Joe King | Joe | King |
3 | Sue Flay | Sue | Flay |
4 | Cory Ander | Cory | Ander |
Handle potential middle names
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:
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:
' formula in C2 and copy down
=RIGHT(A2, LEN(A2) - SEARCH("#", SUBSTITUTE(A2," ", "#", LEN(A2) - LEN(SUBSTITUTE(A2, " ", "")))))
~ | A | B | C |
---|---|---|---|
1 | Full Name | First Name | Last Name |
2 | Barry D. Hatchett | Barry | Hatchett |
3 | Sue Flay | Sue | Flay |
4 | Milo Fletcher Ball | Milo | Ball |
Last_Name, First_Name
If you have a column of names in the Last name, First name format, with the full name in cell A2, the formulas take the following shape:
' first name
=RIGHT(A2, LEN(A2) - SEARCH(" ", A2))
' last name
=LEFT(A2, SEARCH(" ", A2) - 2)
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.
To get a last name, 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.
~ | A | B | C |
---|---|---|---|
1 | Full Name | First Name | Last Name |
2 | King, Joe | Joe | King |
3 | Flay, Sue | Sue | Flay |
4 | Ander, Cory | Cory | Ander |
First_Name Middle_Name Last_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 nicely:
' first name
=LEFT(A2,SEARCH(" ", A2)-1)
' middle name
=MID(A2, SEARCH(" ", A2) + 1, SEARCH(" ", A2, SEARCH(" ", A2)+1) - SEARCH(" ", A2)-1)
' last name
=RIGHT(A2,LEN(A2) - SEARCH(" ", A2, SEARCH(" ", A2,1)+1))
~ | A | B | C | D |
---|---|---|---|---|
1 | Full Name | First Name | Middle Name | Last Name |
2 | Barry D. Hatchett | Barry | D. | Hatchett |
3 | Milo Fletcher Ball | Milo | Fletcher | Ball |
4 | Les Maximus Dickus | Les | Maximus | Dickus |
Last_Name, First_Name Middle_Name
Here are the formulas when the order is Last Name, First Name Middle Name
' first name
=MID(A2, SEARCH(" ",A2) + 1, SEARCH(" ", A2, SEARCH(" ", A2) + 1) - SEARCH(" ", A2) -1)
' middle name
=RIGHT(A2, LEN(A2) - SEARCH(" ", A2, SEARCH(" ", A2, 1)+1))
' last name
=LEFT(A2, SEARCH(" ",A2,1)-2)
~ | A | B | C | D |
---|---|---|---|---|
1 | Full Name | First Name | Middle Name | Last Name |
2 | Hatchett, Barry D. | Barry | D. | Hatchett |
3 | Ball, Milo Fletcher | Milo | Fletcher | Ball |
4 | Dickus, Les Maximus | Les | Maximus | Dickus |
First_Name Last_Name, Suffix
Here’s a similar approach to split names with a suffix
' first name
=LEFT(A2, SEARCH(" ",A2)-1)
' last name
=MID(A2, SEARCH(" ",A2) + 1, SEARCH(",",A2) - SEARCH(" ",A2)-1)
' suffix
=RIGHT(A2, LEN(A2) - SEARCH(" ", A2, SEARCH(" ",A2)+1))
~ | A | B | C | D |
---|---|---|---|---|
1 | Full Name | First Name | Last Name | Suffix |
2 | Joe King, Jr. | Joe | King | Jr. |
3 | Sue Flay, Ph.d | Sue | Flay | Ph.d |
4 | Cory Ander, Sr. | Cory | Ander | Sr. |
Last Name, First Name Suffix
' first name
=MID(A2, SEARCH(" ", A2) + 1, SEARCH(" ", A2, SEARCH(" ", A2)+1) - SEARCH(" ", A2)-1)
' last name
=LEFT(A2, SEARCH(", ", A2) - 1)
' suffix
=RIGHT(A2, LEN(A2) - SEARCH("#", SUBSTITUTE(A2," ", "#", LEN(A2) - LEN(SUBSTITUTE(A2, " ", "")))))
~ | A | B | C | D |
---|---|---|---|---|
1 | Name from Data Dource | First Name | Last Name | Suffix |
2 | King, Joe Mr | Joe | King | Mr |
3 | Flay, Sue Dr | Sue | Flay | Dr |
4 | Ander, Cory Mr | Cory | Ander | Mr |
5 | Fletcher-Ball, Milo Mr | Milo | Fletcher-Ball | Mr |
6 | Sober, Alicia Mrs | Alicia | Sober | Mrs |
7 | Fate, Celia Miss | Celia | Fate | Miss |