Categories
productivity

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

63 replies on “How to separate first names and last names in Apple Numbers App”

where do you type this code?

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

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.

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

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!

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?

Thanks!

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!

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

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

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

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!

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!

I can’t get this one to work. I get a syntax error when I copy and paste this. Can it be placed in any column in row 2?

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.

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?

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!

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

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.