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)



Enjoyed this article?

You can help me by clicking on any other article. People read an article, get the answer they want, and leave. Google call this a 'bounce' and have started penalising sites that people only visit one page on. So please, if this article was helpful, just click back to the front page or to any other article on this site. This says to Google you like this site. Thanks!

11 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 :)

Leave a Reply

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