Jan 09
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…”
” if you 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?
THANK YOU SO MUCH!
Thank you so much for this…this was huge. Just had to update the cells to the cell I was trying to split.
Thank you for this info! This has gotten me SO much closer than I was but I’m not certain that what I’m trying to do is possible. If A1 is Leann Smith and I want B2 to populate LeannS@gmail.com how would I go about doing that? With the info you have above I can only take the first or last name and I haven’t been able to figure out how to get only the last initial. Thanks for your time and skills!
With that second function if you change the 20 to a 1 it should help you out.
With the cells still selected, choose ‘Text to Columns’ from the Numbers > Services menu and choose Space as your delimiter (I’m assuming there’s a space between the first and last name).
https://discussions.apple.com/thread/7467634
Thanks! I was also looking to solve this problem. Thank you again. Cheers!
Just tried it and works like a charm, saved me lots of time. Thank you very much!
a much simpler way to do it.
1- find and replace all spaces with tabs
2- save as CSV
3- open the file, pages will replace tabs by cells