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)



31 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

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

Leave a Reply

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