Today I had a list of full names (eg “Ed Smith”) in a Numbers spreadsheet. I wanted to separate it into first and last name to be able to sort if alphabetically. It was harder than it should be – in my opinion Apple should have a firstname and lastname function! But they don’t. So here’s how to get first name and last name.
After you’ve done this if you want to delete the original full names you’ll need to ‘copy’ then ‘paste values’ over the formula.
The Original full name is in the cell ‘B2’
Firstname:
=LEFT(B2,(SEARCH(" ",B2)-1))
Lastname:
=MID(B2,(SEARCH(" ",B2)+1),20)
Explanation
The LEFT function takes the first digits of the cell, up until a certain point.
The SEARCH function looks for a space ” “.
So the Firstname function I made above is just taking all the letters up until the first space. It needs a ‘-1’ in the formula or else it would take the space as well.
The MID function grabs all the letters from a certain point on.
So this takes the next 20 letters after the space. (The +1 means start after the space).
Update: Middlename
OK, in reply to a question below, which I took as a challenge, here’s how to extract a middle name:
MID(B2,(SEARCH(" ",B2)+1),(((SEARCH(" ",B2,(SEARCH(" ",B2,1)+1)))−(SEARCH(" ",B2,1)+1))))
And here is a better way to get ‘lastname’ if you have a list of names that may have an initial or a middle name.
RIGHT(B2,(SEARCH(" ",B2)))
Leave a Reply