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’


=LEFT(B2,(SEARCH(" ",B2)-1))


=MID(B2,(SEARCH(" ",B2)+1),20)
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)))

65 Responses to “How to separate first names and last names in Apple Numbers App”

  1. numbers user KW says:

    where do you type this code?

    you have listed
    First name:
    =LEFT(B2,(SEARCH(” “,B2)-1))
    last name:
    =MID(B2,(SEARCH(” “,B2)+1),20)

    • Wayne says:

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

      • Jan says:

        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.

      • Wayne says:

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

  2. Anne says:

    Awesome – you’re a lifesaver! Thank you.


  3. Vicki says:

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

  4. Adam says:

    This worked perfectly. Thank you!

  5. Neil Asher says:

    Perfect thank you!

  6. Lorraine says:

    If I have cells with entries like
    123 E Main St
    Jane T Doe and I want those to become
    123 | E Main St
    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.

  7. Tom Hutchinson says:

    Life Saver – Thanks :)

  8. Angela says:

    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!

  9. Jessica says:

    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?


  10. 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!

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

  12. James says:

    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!!!

    • Wayne says:

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

      • James says:

        I don’t know what copy and paste values means… sorry for being ignorant to these details

      • Kevin says:

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

      • Wayne says:

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

  13. Stephen says:

    You sir, are a legend.

  14. William says:

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

  15. HC says:

    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!

  16. gemlyrics says:

    Useful info. Thanks that you helped me.

  17. gemlyrics says:

    Its very useful info. Thanks

  18. Kentavo says:

    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!

  19. Matthew Johnson says:

    Brillant! Thank you!!

  20. Jennifer says:

    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.

  21. Cindy says:

    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?

  22. Danielle says:


  23. Travis says:

    Thank you so much for this…this was huge. Just had to update the cells to the cell I was trying to split.

  24. Leann says:

    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!

  25. Wayne says:

    With that second function if you change the 20 to a 1 it should help you out.

  26. Spire says:

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


  27. Maria Gill says:

    Thanks! I was also looking to solve this problem. Thank you again. Cheers!

  28. Jeff says:

    Just tried it and works like a charm, saved me lots of time. Thank you very much!

  29. Michel says:

    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

Leave a Reply

Copyright © 2013 Wayne Connor. All rights reserved. | Hosted on bluehost.com Click here to find out why.
preload preload preload