Separate Full Name into First & Last Name
- 74 Comment
Here are two easy formulas that will separate a cell that contains a full name (first and last name format) into 2 cells – one containing the first name and the other containing the last name. To do this we are going to make use of the left, right, len & find formulas.
Extract First Name from Full Name
To extract the first name from cell A1, which contains the full name, we use the formula as follows:
=LEFT(A1,FIND(” “,A1)-1)
The way this work is the find formula returns the number of characters where it finds the first space – we then subtract one from that formula because we want the location of the last non-space character. The Left formula then takes the first characters equal to the number returned by FIND()-1.
Extract Last Name from Full Name
To get the last name from the full name, we are going to piggyback off the original formula from above. Using the following formula will extract the last name from full name:
=RIGHT(A1,LEN(A1)-FIND(” “,A1))
Let’s take a look at this formula. The first thing that is done is we find the total length of the cell contents of A1. Next, the character count of the location of the first space is subtracted from the total length of A1. This should leave us with the length of the last name. The final step is to take the Right formula and select the number of characters in the length of the last name of cell A1.
Maybe taking a look at the examples will make it clearer.
Example
Cell A1 contains the full name “John Smith”
First Name Formula: =LEFT(A1,FIND(” “,A1)-1) – this will return “John”.
Last Name Formula: =RIGHT(A1,LEN(A1)-FIND(” “,A1)) – this will return “Smith”.
Easy right? Be careful though, these formulas will only work in the format of Full Name = First Name + Space + Last Name. Lets take a look at what happens if we have the format – Full Name = First Name + Space + Middle Name + Space + Last Name.
Full Name = “John Daniel Smith”
First Name Formula: =LEFT(A1,FIND(” “,A1)-1) – this will return “John”.
Last Name Formula: =RIGHT(A1,LEN(A1)-FIND(” “,A1)) – this will return “Daniel Smith”.
As you can see, the first name formula works correctly. However, the last name formula will return both the middle and last name. We will return to this in a later post to explore how to get all three names out of the Full Name in that format.
Random Posts
74 Comments on this post
Trackbacks
-
Rod Merrill said:
There’s a much easier method for splitting names in Excel 2007 by using the Text to Columns feature. Go to http://www.brainstorminc.com/cbt/microsoft/help.php?file=excel15 to watch what I believe is an excellent free instructional video.
January 25th, 2009 at 9:17 am -
john said:
Thanks Rod for the suggestion. You are correct that the text to columns feature can be used in many circumstances to quickly break out cells into multiple cells.
Sometimes however you may need a little more flexibility and the formula might be the way to go. Thanks for visiting Excel Hints and suggesting the video.
January 25th, 2009 at 12:40 pm -
john said:
I also believe the Text to Columns feature is available in previous versions of Excel as well. Thanks again for the suggestion Rod.
I might have to write a post about that soon showing how that feature works – its very similar to import a delimited file.
January 25th, 2009 at 12:44 pm -
Paul said:
Hi John,
This has been helpful. Thank you.
Have you posted getting all three names out as you mentioned? I am working with a client whose name data is in multiple formats.Example name1 – Larry W & Sharon R Smith
Example name2 – Larry Smith
Example name3 – Larry W SmithThis is a common formatting issue in the back office systems in my niche.
Thanks again,
PaulMay 1st, 2009 at 10:35 am -
john said:
Hi Paul…
If the names are consistantly in one of those three formats, you could easily probably write different formulas for the different types of name formats to extra the names correctly.
I have not posted an example like this yet, but if you want to shoot me an email with an example of what you’re looking for I can take a look.
May 1st, 2009 at 10:43 am -
Ryan said:
I am not having luck with these formulas in 2003, is this a new to 2007 feature?
July 14th, 2009 at 5:20 pm -
Ryan said:
Nevermind on the above comment I found a slight variation that seems to work for me. Thanks for the tip ive been trying to find something similar all day.
To get last name
=MID(A1,FIND(” “,A1)+1,LEN(A1))To get first name
=LEFT(A1,FIND(” “,A1))July 14th, 2009 at 5:26 pm -
Jazz said:
Great formula, brought back many memories of when I used to work for a mailing house and I used this code almost everyday as the data most clients sent was awful.
It’s simple and very very effective, even when dealing with 1,000′s of records.
September 8th, 2009 at 6:50 am -
Greg said:
Hi,
Here is another way to get the Last name (or word):
=TRIM(RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,”"))))))
October 7th, 2009 at 4:50 pm -
john said:
Good one Greg!
October 7th, 2009 at 9:28 pm -
Chantal said:
Thanks, this help was wonderful!
October 22nd, 2009 at 2:43 pm -
Rick said:
Genius Greg, thanks!
November 2nd, 2009 at 12:49 pm -
tom said:
good tip. thanks for sharing.
January 26th, 2010 at 5:11 pm -
Rich said:
Hi there,
Firstly, great suggestions, very helpful. However, I’m still slightly stuck..
I have one column with names in this format
Jones, Tom
Blogs, Joe
etcI need to create a new column in the following format
JonesT
BlogsJCan anybody help ??
Many thanks
R
March 17th, 2010 at 11:00 am -
john said:
Hi Rich, try this formula. There are probably several ways to do that, but here’s one:
=LEFT(A1,FIND(“, “,A1)-1)&MID(A1,FIND(“, “,A1)+2,1)
March 17th, 2010 at 12:46 pm -
Rich said:
John, that is tremendous! Does exactly what i need it to! Not an excel person, that would have taken me weeks to figure out
Many thanks
Rich
March 17th, 2010 at 1:00 pm -
Louisa said:
Thanks, that’s very helpful. However, I have a report that have all different kinda names, including personal names and company names. i.e:
1. ABC, LLC
2. ABC Corporation
3. Doe David
4. Doe Mary C.
5. ABC TrustFor those company and Trust names, I want to leave it as it is, no need to split last name and first name, but for those personal names,I do want to split last and first names. Is there any function that I could fix the whole column?
April 16th, 2010 at 4:17 am -
tanya said:
How about changing Smith, John to John Smith?
Any help would be appreciated!!
thanks, tanya
April 22nd, 2010 at 3:54 pm -
sarina said:
Hi.
I would so appreciate the formula to turn:John Smith
into
Smith, John
Thanks!
April 29th, 2010 at 2:49 pm -
john said:
Try this:
=RIGHT(A1,LEN(A1)-FIND(” “,A1)) &”, “&LEFT(A1,FIND(” “,A1)-1)
April 29th, 2010 at 2:53 pm -
sarina said:
Hi.
Thank you for the help already.
Just thought I’d try again, if anyone knows how to take something like this:John Smith
3456 Doe Street
Kalamazoo, ZK 35605and turn it into 5 columns (one just for the name, one just for the street address, one for city, one for state, one for zip
I get tons of info that looks like the above, but need to enter it in 5 columns, and I fear some carpal tunnel.
My best bet so far has been to cut & paste it individually, but I wondered if I enter it all at once (as is, in one clump) if there was a way to get Excel to break it up for me.
Any advice/info would be greatly appreciated.
Thanks!
SarinaMay 10th, 2010 at 10:39 am -
john said:
Hi Sarina,
Are you placing all of that into 1 cell currently?
May 10th, 2010 at 11:22 am -
sarina said:
I cut and paste it into 5 cells, but would love to drop it in one cell and then have it broken up into the 5 cells for me or whatever else you can recommend. It comes in that arrangement as written – 3 lines of info.
May 10th, 2010 at 12:06 pm -
tanya said:
thank you for the
=RIGHT(A1,LEN(A1)-FIND(” “,A1)) &”, “&LEFT(A1,FIND(” “,A1)-1)formula!
I’m able to “Smith, John” into “John Smith,”. Can you tell me how to remove the comma at the end?
Please and thank you!!
tanyaMay 20th, 2010 at 2:08 pm -
john said:
Hi Tanya,
There are many ways to do this, try this:
=RIGHT(A1,LEN(A1)-FIND(“,”,A1)-1) &” “&LEFT(A1,FIND(“,”,A1)-1)
You might also want to look into the Text to Columns feature of Excel.
May 20th, 2010 at 2:17 pm -
tanya said:
JOHN YOU ARE AWESOME!!!!
Thank you so much for the quick response and saving my hours of copy&pasting!!!
May 20th, 2010 at 2:27 pm -
Ajay said:
if i have name like Ajay Kumar Yadav and i need to separate every name like
Ajay
Kumar
YadavPlease advise
June 24th, 2010 at 12:34 am -
Deb said:
I have one, my info is John A. Smith, MD and I need it to be Smith, John A DO or even into indivicual columns, whichever is easier. Anyone able to help me with this? Thanks much for looking!
June 25th, 2010 at 9:37 am -
Bis said:
To seperate the last name, I use:
=RIGHT(A1,LEN(A1)-FIND(” “,A1))Sometime the names can appear as FirstName:LastName in stead of a space in-between, so the seperator can be updated in the formula
=RIGHT(A1,LEN(A1)-FIND(“:”,A1))June 28th, 2010 at 8:34 am -
BrianK said:
I use this for splitting things such as location/person where I have a timesheet of information and I need to just know the locations/names serperately.
if you have Plant32/Johnson in cell a1, we’ll call a1 the source
….johnson is working in plant32…for the location:
=left(source,find(“/”,source)-1)
where source is the cell which contains the information you want to splitfor the names:
=right(source,(len(source)-find(“/”,source)))
where source is the cell which contains the information you want to splitperhaps there is a different way. i use this to track 15 or so substitute custodians in a school system for time sheet, etc. anyone interested I can e-mail the sheets.
July 13th, 2010 at 7:47 pm -
BrianK said:
Deb,
for John A. Smith, MD
in ecel 2007 you could use Convert text to table
select space as the delimeter
then you would have them in seperate columns, however it would look as you see it… John A. Smith, MD where each space = new column. Moving the Smith column could be done easily enough or did you need a formula to simply do the whole thing?July 13th, 2010 at 7:57 pm -
BrianK said:
Ajay,
Here is what I tried, to seperate the name, assuming that is a middle name or whatever, use these formulas. We’ll call the location of Ajay Kumar Yadav the “source”
To seperate the parts I’ll call them LeftPart, CenterPart, RightPartfor the LeftPart use:
=LEFT(source,FIND(” “,source)-1)for the CenterPart use:
=IF(ISERR(FIND(” “,source,1+FIND(” “,source))),”",MID(source,FIND(” “,source)+1,FIND(” “,source,1+FIND(” “,source))-FIND(” “,source)-1))for the RightPart use:
=IF(ISERR(FIND(” “,source,1+FIND(” “,source))),MID(source,FIND(” “,source)+1,LEN(source)),MID(source,1+FIND(” “,source,1+FIND(” “,source)),LEN(source)))phew… hope that helps.
Put the formulas in the column where you want to split the name upJuly 13th, 2010 at 8:20 pm -
Chris said:
I am using this formula =LEFT(A6,FIND(” “,A6)-1) on this text located in A6: 1001 Bermuda Bag Alligator. I would like to separate the 1001 from the rest of the text. I receive this error message #NAME.
I am not sure what I am doing wrong? I am using excel 2007.
August 17th, 2010 at 9:22 am -
Shebra said:
Can anyone provide a formula for “Smith,John”? There are no spaces. I would like the last name in a seperate cell minus the coma.
September 23rd, 2010 at 12:20 pm -
Faizal said:
I have a 14 character no eg: 20001016862200, from this I have to extract 101686 the formula will be =MID(A5,4,6). But I need the result in 19861610 instead of 101686.That last two characters 86 prefixed by value “19″, then mid value 16 and the last value 10.Can anybody help me on this. Thanks in advance
November 15th, 2010 at 8:01 am -
john said:
Try this…
=”19″&MID(A5,9,2)&MID(A5,7,2)&MID(A5,5,2)
November 15th, 2010 at 5:12 pm -
andrea said:
Everytime I try to use the formulas above I keep getting #NAME?
Can someone help tell me what I’m doing wrong?
November 19th, 2010 at 11:59 am -
javie said:
to get the firstname try this =LEFT(A1,LEN(A1)-FIND(” “,A1,1))
January 6th, 2011 at 9:36 am -
andrea said:
andrea,
retype the ” ” in each instance. they are intalicized for some reason when you cut/paste.
January 8th, 2011 at 3:41 pm -
Linda said:
How can I change “Smith, Linda A” into “Linda Smith”? Thanks!
January 11th, 2011 at 8:22 pm -
tom said:
i already split the first name and last name, what if there is in the list that include only the last name(without last name)
example:1 john smith
2 mark twain
3 arnoldi used LEFT(A1,FIND(” “,A1)-1) for 1 and 2,is there a formula for me to get the first name onlly for no.3 using the same formula for 1 and 2?? because when i used the formula for no3 i get an error.
thanks
January 14th, 2011 at 7:55 am -
John From Raleigh said:
I ended up using the text to columns feature and it worked great. I have Excel 2003.
Thanks for the tip….
January 18th, 2011 at 1:04 pm -
Rachael said:
I have cells where the Name field was pulled from an external database. The names show up like this: JohnSmith. No space between first and last name, however the start of the last name is always in caps. How can I separate these into separate cells that read John Smith? I need a column heading of First Name and a column heading of Last Name, but there are over 5,000 names and I cannot go through each individual cell to separate them! Also, there are blank rows between each record, is there a way I can delete every other row without having to highlight each individual one? This is a huge spreadsheet and I am trying to complete it quickly and with accuracy. Thanks!
January 19th, 2011 at 12:16 pm -
Daniel said:
Hi, this is great, but your website seems to convert straight quotes into smart quotes (curly quotes). The quotation marks must be straight quotes or else Excel gives a #NAME? error. It’s the content management system or rich text editor on the site that usually does that. Probably worth fixing since it seems like this problem would happen a lot on this kind of site.
February 2nd, 2011 at 9:21 pm -
Jamie said:
Not working for me. I keep getting the ?Name error in my cell.
Using Excel 2007.
February 8th, 2011 at 8:00 pm -
Meena said:
I spent quite a bit of time experimenting the above samples. It didn’t work for me. All I wish to do is separate/reverse this in column A2 of my data sheet: Loretta Smith
I tried this string =MID(A2& “, ” &A2, FIND(” “,A2) +1, LEN(A2)-1), but it keeps returning a partial result of her First Name such as
(Smith, Loret). What am I missing from the formula to have it include the full spelling of this name?Any help is greatly appreciated. Thanks!
March 2nd, 2011 at 7:53 pm -
Ellen said:
I have tried all of the above option, I have changed manually the smart quotes into straight quotes and I still get #name? Anybody got a solution as I dont want to split 350 names in first names and surnames manually!
June 8th, 2011 at 10:21 am -
john said:
Shoot me an email from the contact form with what you tried and let me take a look… I know there’s been a problem with the quotes on the site, I’m trying to fix soon hopefully.
June 8th, 2011 at 11:09 am -
Ellen said:
Thanks John could you let me have your email address pls
June 9th, 2011 at 3:14 am -
vinay said:
I have a name called “Sanjay Kumar Gupta”…i want to get firstname and middlename in one column and lastname in second column. can anybody help in getting it
Thanks in advance!!
July 8th, 2011 at 2:24 am -
john said:
Look into “Text to Columns” feature of Excel… with space as the delimiter.
July 8th, 2011 at 9:34 pm -
Vinicius Camara said:
First of all, congratulations. This post is awesome!
In my excel works but I needed change function separators:
=LEFT(A2;FIND(” “; A2)-1)
=RIGHT(A2;LEN(A2)-FIND(” “;A2))Thank you.
August 3rd, 2011 at 11:24 am -
Varun said:
If we use =LEFT(A2;LEN(A2)-FIND(” “;A2)) to get the first name then why dont get the entire first name.
i am not an excel person so would be great if the similar formula is used for both left and right words.August 4th, 2011 at 8:46 am -
Lily said:
Hi John,
I’m working on a project with a tight deadline.
Can you kindly let me know what the formula would be for converting
KENDALL J. Smithto
Smith, KENDALL J.
Many thanks in advance!
August 15th, 2011 at 3:16 pm -
john said:
Try something like this to find the last space, then to the left is the first/middle and to the right is the last name…
=LEN(A1)-LEN(RIGHT(A1,LEN(A1)-SEARCH(“@”,SUBSTITUTE(A1,”
“,”@”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,”"))))))August 16th, 2011 at 10:27 am -
Jessica said:
Is it possible to get First Name Last Name format from multi-entry data, such as in example below:
Lepter David|Nilesh Raj|Swen Michael|Kamil Raj|Ouder Gerald|Mick Bryan|Tout Frank|Egrin Joseph|Hartman Phil
Format needed would be:
David Lepter|Raj Nilesh|Michael Swen|Raj Kamil|Ouder Gerald|Bryan Mick|Frank Tout|Joseph Egrin|Phil Hartman
Thanks very much for any input you may have on this.
August 21st, 2011 at 9:56 am -
Man Prasad Chowai said:
I have a name called “ram bhakta shrestha subedi”.
I want to extract only the last word subedi.
how can i do this?September 7th, 2011 at 4:30 am -
Tim said:
I have two challenges in some data cleansing of a list which includes 15,000 entries. Therefore, any help would be greatly appreciated.
1. I need to separate Last from First. No space but the first name is Capitalized. Is there anyway to identify a capital letter then move the rest of the name to another column. Here’s a sample:
MerrittBecky
LamoreauxRebecca
KageBrian
BelcherGordon
EliasonWilliam
SkillenBelinda
ElliottBlake2. I have emails that have double entries. Therefore, is there a way to cut off all existing letters after ‘.com”
Many thanks for help.
Tim
September 12th, 2011 at 2:59 pm -
Dayana said:
HI
please help , need to seperate first and last name
James p kamath
Antony pinto Alex
D Anjelena Fetura margret
thanks
dayana meannSeptember 22nd, 2011 at 9:10 am -
asas said:
Hi. Could somebody help me with this? I have name John Smith and I need put in another cell but like this “[John][Smith]“
October 24th, 2011 at 11:47 am -
Rose said:
For some reason, I cannot get the formulas to work. The answer I get is: #NAME?
Can you help? Thanks.
October 26th, 2011 at 3:57 pm -
Julie said:
I am working on a complex list of names such as
Mr & Mrs Douglas Winston
Mr. John F. Smith
Dr. & Mrs. Jack F. DoeI just want to separate the last names so I can sort by last name. All the formulas I’ve tried give me more than just the last name.
How can I do this? Using Excel 2007.
ThanksOctober 31st, 2011 at 1:59 pm -
Christina Haller said:
I need to separate John & Amy Smith from one cell to John & Amy in one cell and Smith in the second. Any tips?
November 4th, 2011 at 3:05 pm -
Karen Schrimpf said:
I too need to find out how to separate a couples 1st names out of a mailing list to be used in a mail merge for a salutation. for example
Ken & Barbie Doll – I just need Ken & Barbie.November 7th, 2011 at 1:14 pm -
john said:
To get the last word in a cell try using a formula such as this: =RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,”"))))) . That is assuming the last name is only a single word and is at the end (suffixes can mess that idea up)
November 8th, 2011 at 7:41 am -
john said:
I would use the Text To Columns feature of excel and use space as the delimiter. Then you can work on recombining what you want. Or possibly using the formula: =RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,”"))))) to find the last name in combination with the find & left formulas to only show the first names.
November 8th, 2011 at 7:43 am -
My Hoa said:
Thank you very much john. You’re so genius^^
November 8th, 2011 at 11:14 pm -
Sanjeev said:
Hi,
I Just want know how to change Last Name as first in single cell.Example.
Sanjeev Shrikant Jahagirdar
now i want to change some thing like below
Jahagirdar Sanjeev Shrikant
So Please help on this
Regards
S.S.JahagirdarNovember 12th, 2011 at 2:25 am -
ABDULSALAM said:
I need command for middle name (example: firstname + middle name + last name.
pls show me how to split it
December 15th, 2011 at 2:00 am -
ABDULSALAM said:
Please show me how to split this full name to first name, middle name & last name
DULAL SHIKDER MD AYNAL DULAL SHIKDER MD AYNAL
December 15th, 2011 at 2:02 am -
SACHIN said:
What is the last cell name in M.S. Excel Worksheet?
December 27th, 2011 at 10:39 am -
MAV said:
Hi – I have a formula that references a cell in a name column for selecting a same-named worksheet tab as part of a cell-reference in the same-named worksheet. The formula is as shown below and works great – except when the name is one that includes a quote mark, e.g. Frank O’Hara. How to adjust formula to fix this problem?
=SUM(INDIRECT(“‘”&A2&”‘!$V$57″))
January 25th, 2012 at 11:52 am
[...] post can be seen as a follow up to an earlier post, Separate Full Name into First & Last Name. Excel provides a very easy to use tool to separate delimited data into multiple columns. All [...]
[...] found it Excel Hints what a concept eh? Better suscribe to that blog right [...]