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)))`

where do you type this code?

you have listed

First name:

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

last name:

=MID(B2,(SEARCH(” “,B2)+1),20)

it goes into the actual cell. You can enter a formula in a cell, the ‘=’ means it’s a formula.

I am copying and pasting your formula and changing your b2 to my cell which contains the full name (John Doe). However, nothing is populating except the formula is showing. What am I doing wrong? Thanks.

That should work – are you changing both ‘b2’ references?

Awesome – you’re a lifesaver! Thank you.

anne

Thank you! Save me a lot of work and didn’t have to go to Excel!!!

This worked perfectly. Thank you!

Perfect thank you!

If I have cells with entries like

123 E Main St

and

Jane T Doe and I want those to become

123 | E Main St

and

Jane T | Doe

how would I do that? In other words… when I have more elements than just the first & last name, or when the address includes a street direction and a “st” or “ave” etc.

Thank’s..

It Works..

Life Saver – Thanks :)

I need a little extra clarification for this part, please: “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.”

If I copy all the names in B2 then how do I paste values over the formula? Thank you so much!

Hey, thanks so much for this. I’ve got it to work for cell B2 but how do I duplicate the formula for the whole list without having to enter a new formula for each row?

Thanks!

Just copy it down and it will duplicate the formula automatically.

One question: If my “B2” cell currently looks like “Smith, John”, how can I get rid of the comma? When I copy/paste your formula, the LAST NAME column is retaining the comma, so looks like, “Smith,”.

Thanks in advance for your help! Other than the comma, this worked perfectly!

I actually just answered my own question. I changed “-1” to “-2”

=)

Well done!

I don’t understand your instruction for deleting the original column after splitting its contents. When I delete the original column or any of the cell data individually I get the error icon that looks like a yield sign with a red exclamation point inside… HELP!!!

You don’t have to delete them, it says “if…”

”

ifyou want to delete the original full names you’ll need to ‘copy’ then ‘paste values’ over the formula.”I don’t know what copy and paste values means… sorry for being ignorant to these details

Can you explain “Values?” I would like to delete the original Full Name column but I get an error when I do.

‘paste values’ pastes the result of the calculation into the cell rather than the formula itself.

You sir, are a legend.

Please could you help with separating by a carriage return in the cell.

Hi there,

We have exported an email list from our website and the export function has zero options. We simply want to extract a list of email addresses. Below is an example of what turns up in a single cell in the Mac program ‘Numbers’. Is there a formula to use to delete everything before the first semicolon in each cell?

Thank you. Any help would be greatly appreciated!

Never mind! We have sorted the export issue by updating the widget :)Thanks

Great.

Your best bet would be to use the free app called “text wrangler”. It’s great for stuff like that.

Useful info. Thanks that you helped me.

Its very useful info. Thanks

My list of names has some middle initials (Joe C. Clark) as well as some middle names (Joe Chadwick Clark). Anyone know the magic trick to either skip this middle info or dump it into it’s own column? Thanks in advance!

tricky! Try this:

=MID(B2,(SEARCH(” “,B2,(SEARCH(” “,B2)+1))+1),20)

I can’t get this one to work. I get a syntax error when I copy and paste this. Can it be placed in any column in row 2?

Brillant! Thank you!!

Still need some help. I have the last name, first name in column A. so I need to know where do I copy and past the formula too? And I need it to remove the comma separating the name, is that possible. Also do I need to have a blank column for B and C so it places the separated name on those columns. I wish there was a video. I am a visual learner ;-) Thank you so much for your time. I really appreciate it.

This was pretty useful, however, the names I need to extract are names that appear on driver’s licenses. Some have middle initials, some middle names some neither.

How can I extract First, Middle (if it exists) and Last?