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