How to Separate First & Last Names in Excel & Apple Numbers
How to Separate First & Last Names in Excel & Apple Numbers

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 list to get it done.

The Original full name is in the cell ‘B2’

Create a new column called ‘First Name’, and enter the formula below:

Firstname:
=LEFT(B2,(SEARCH(” “,B2)-1))

Create a new column called ‘Last Name’, and enter the formula below:

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 ” “.
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.