How to separate first names and last names in Apple Numbers App

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

Posted

,

No related posts.

New Posts

Latest Comments

  2. One Last question, how did you filter your overdue smart list. I have been using relative range past year but…

Article Categories

Comments

67 responses to “How to separate first names and last names in Apple Numbers App”

  1. Stephanie

    This helped tremendously. Thank you!

    Reply
  2. Mark Stegman

    Thank you. This formula for separating first and last names on an Apple Spreadsheet Page worked fast and easy. Thanks for sharing.

    Reply
  3. Bastion

    You know that the “better way” to extract the last name doesn’t work at all, right?

    RIGHT(B2,(SEARCH(” “,B2)))

    This search finds the position of the FIRST space when counting from the LEFT. You need to take the length of the whole string and subtract the found position, then you can use that number to extract from the right.

    This ONLY works, though, when the name has just two parts (because, again, the search starts from the LEFT and finds the first word break) – middle names, titles, multiple non-hyphenated surnames, or ordinals at the end will still mess it up.

    Reply
    1. Wayne

      I see what you mean!

      Reply
    2. Bastion

      PS: I found the solution that works: :-D

      =RIGHT(FullName,
      LEN(FullName)−SEARCH(“#”,
      SUBSTITUTE(FullName,” “,”#”,
      LEN(FullName)−LEN(SUBSTITUTE(FullName,” “,””)))))

      What it does is first subtract from the length of the original FullName the length of the substituted FullName where all spaces were removed. This tells you how many word breaks there are (or at least number of spaces, in case you have some double-spaced breaks – but it works for this purpose regardless).

      With that count you throw that right back into a substitute on the FullName to change the last space into a “#” (use whatever tagging character works for your data). Now you have a marker for the last word.

      Using search you can find the position of the “#”, which when again subtracted from the original FullName’s length gives you the size of the last word, so that you can finally bite that many characters into the FullName from the right.

      Enjoy!

      Reply
      1. Wayne

        Good work – what a formula!!!

  4. Jojo

    It worked! Thank you

    Reply
  5. Emily Steele

    WOW I have to say THANK YOU this was so so helpful.

    Reply
  6. Katie

    Thank you so much!!!

    Reply
  7. Paul Smith

    Thanks, but when I try to delete the original full name column it puts those triangles with the exclamation mark in new columns. I guess the new columns are pulling from the original? How do I get rid of original without messing up the new? Make sense?

    Reply
    1. Wayne

      You need to ‘copy’ then ‘paste values’ so it pastes the results not the formula.

      Reply
  8. donna

    My spreadsheet has several rows. How do I apply the equation for the entire column?

    Reply
    1. Kells

      There is a much easier way to do this now!!!

      First you can use the Find and replace tool (Command+F)
      In the Find section put the space or comma (whatever is separating the names)
      In the Replace section replace it with ,SPACE just use the space key don’t type the word SPACE
      Click replace all.

      Copy the entire column and paste it into a Text editor like Text Edit and save the text document.

      Open the text document in numbers and when you do a prompt opens at the top of your document asking if you want to adjust the import settings. Click on Adjust Settings.
      Make sure that it is set to “Delimited”
      Choose ,(comma) as your delimiter

      This should separate the names.

      A good resource for this is in this video.
      https://macmost.com/numbers-text-import-options.html

      Reply
  9. Boyan

    No matter what I try, I get a red triangle with exclamation mark saying “The formula contains a syntax error”.

    Reply
  10. Kells

    There is a much easier way to do this now!!!

    First you can use the Find and replace tool (Command+F)
    In the Find section put the space or comma (whatever is separating the names)
    In the Replace section replace it with ,SPACE just use the space key don’t type the word SPACE
    Click replace all.

    Copy the entire column and paste it into a Text editor like Text Edit and save the text document.

    Open the text document in numbers and when you do a prompt opens at the top of your document asking if you want to adjust the import settings. Click on Adjust Settings.
    Make sure that it is set to “Delimited”
    Choose ,(comma) as your delimiter

    This should separate the names.

    A good resource for this is in this video.
    https://macmost.com/numbers-text-import-options.html

    Reply
    1. John

      THIS is it!!!!
      Brilliant.

      Reply
    2. Melody

      I have followed these steps, but can’t get Numbers to open the TextEdit file. It is grayed out. What am I doing wrong?

      Reply
    3. Mike

      Awesome. Thank you. Make sure to convert the text edit file to “Plain text” before saving or you cannot open the document in numbers.

      Reply
  11. Eric C.

    You are a Ninja! Thank you for such an easy fix to 2,500 names on a spreadsheet that I received for Leads….

    Reply
  12. Lou Lifrieri

    Original formula worked like a charm. Thanks!

    Reply
  13. Kristina Schmidt

    Hey ty! Totally worked.

    FIX FOR FORMULA /TEST ISSUE, just open NOTES, like apple Notes app and start one cell of a table. Then copy and paste the first name column, then last name column. It will show as text values. Copy from Notes and paste back in Numbers and it will be text, not formulas

    Reply
  14. Julian

    Hello,
    where do you write the formula! thanks

    Reply
    1. Wayne

      You write it in the cell where you want the name to go. Next to the full name.

      Reply
Older Comments
1 2

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.