How to Separate First Names and Last Names in Excel and Apple Numbers
But I Need First and Last Names in Different Cells!
Often times, you may find yourself with a list of full names in a Numbers Or Excel spreadsheet (ex: “Jane Doe”). If you want to sort this list alphabetically, you’ll need to separate the names into first and last name columns. Unfortunately, there is no first name and last name function to help with this. Here’s a step-by-step how-to explaining how to separate first and last names in Excel and Apple Numbers.
The Original full name is in the cell ‘B2’
Create a new column called ‘First Name’, and enter the formula below:
Create a new column called ‘Last Name’, and enter the formula below:
The LEFT function takes the first digits of the cell, up until a certain point.
The SEARCH function looks for a space ” “.
The Firstname function is 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.
This takes the next 20 letters after the space. (The +1 means start after the space).
Stuck with Middle Names? Here’s how to remove them:
MID(B2,(SEARCH(” “,B2)+1),(((SEARCH(” “,B2,(SEARCH(” “,B2,1)+1)))−(SEARCH(” “,B2,1)+1))))
Note: After following these steps, if you want to delete the original full names you will have to “copy” and then “paste values” over the formula.
This method can be used for separating any data that is confined to a single cell to two or more cells. This can be particularly helpful when pasting lists created in Microsoft Word into Excel or Numbers.
If you enjoyed reading this article, please check out some more in our Knowledge Base.