Jan 09

numbers

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)

 

 

Enjoyed this article?

Click back to the front page to see some of my latest posts. Thanks!

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

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

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

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

  4. Stephen says:

    You sir, are a legend.

  5. William says:

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

Leave a Reply

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