Most attempts to split a full name into first/last split by the first space but this fails when people also have middle names. Here’s how to do it properly.
Firstly, are you sure that first and last name is actually what you want? If there’s a chance your data includes people with middle names then what you actually want is to split into given name(s) and family name, but this is a bit trickier.
First let’s see how to split a name in the unlikely event that everyone has but a single first and last name.
Splitting First and Last name
Meet John Peters. He doesn’t exist, nor does he need to. Let’s split his name:
- Find the position of the space
- Everything up to that position is the first name
- Everything after that position is the last name
The formula for finding his first name is =LEFT(A2, FIND(" ", A2)) and to find his last name we use =RIGHT(A2, FIND(" ", A2) + 1). These formulae are fairly simple; LEFT gets a substring starting from the start, RIGHT gets a substring starting from the end and FIND returns the position of the given string, in this case a space.
You may have to replace references to A1 if the full name is in another cell. Also note that we add one to the position of the last name because that’s just how numbers work.
“John Peters” is now “John” and “Peters”. Simple, but incomplete:
Splitting Given and Family names
Here’s the rub: peoples’ names aren’t always that simple.
Meet Jason Gordon-Lee Rogers. He doesn’t exist either but that isn’t going to stop us!
His last name is Rogers and his given names are Jason Gordon-Lee, so the formula above won’t work because it’ll place his middle name with his family name when we actually need it to be part of his given name.
Let’s split his name:
- Do some voodoo witchcraft I found on stackoverflow to get his last name (everything after the last space)
- Remove his last name from his full name to find his given names
The formula for finding his family name is =RIGHT(A2,LEN(A2)-FIND("|",SUBSTITUTE(A2," ","|", LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))). I know, right. Just trust me, it works.
Then the easy part: use the SUBSTITUTE function to take the full name and replace the family name with an empty string: =SUBSTITUTE(A2, CONCATENATE(" ", C2), ""), where A2 is the full name and C2 is the family name.
Here’s what we get:
