How to split full name to first and last name in Excel?

Supposing you have a name roster as the first screen shot shows in single column below, and you need to split the full name to the first name column and last name column as the second screen shot shows.

shot-split-names-1arrow-bigshot-split-names-2

Here are two tricky methods to help you solve this problem:

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

arrow blue right bubbleSplit full name to first and last name with functions

You can use the Left function, Right function and Find function to spit a full name to the first name and last name with following steps:
Step 1: Enter the formula of =LEFT(A1,FIND(" ",A1,1)-1) in a blank cell, says Cell C1 in this case.

shot-split-names-3

Step 2: Enter the formula of =RIGHT(A1,LEN(A1)-FIND(" ",A1,1)) in another blank cell, Cell D1.

shot-split-names-4

Step 3: Select the range of C1:D13, and click the Home >> Fill >> Down.

Now you will see the full name column is split to the first name column and last name column at once.

Click to know how to merge the first name column and last name column into one column.


arrow blue right bubbleSplit full name to first and last name with Text to Column command

It is not easy to remember the function. Please do not worry about it. The second way will guild you to split the full name column easily.
Step 1: Select the column that you will split, in this case it is A1:A13.

Step 2: Click the Text to Column button under Data Tab.

Step 3: In the Convert Text to Columns Wizard – Step 1 of 3 dialog box, check the Delimited option, and click Next button.

Step 4: in the Step 2 of 3, only check the Space option in the Delimiters section, and click Next button.

shot-split-names-8

Step 5: in the step 3 of 3, check the Text option in the Column data format section, and specify the Destination cell.

shot-split-names-9

Step 6: Click the Finish button.

Then you will see the full name column in separated into first name column and last name column at once.

Click to know how to merge the first name column and last name column into one column.


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Comments  

+2#Kaushik2013-11-28 03:59
awesum ... did help alot ... thxxx
Reply | Reply with quote | Quote
+3#mel2014-01-27 15:54
Life Saver!! Thanks for the Demo!!
Reply | Reply with quote | Quote
+1#Josh2014-02-04 15:20
this only works if the people on your list don't have middle names! ahh so frustrating
Reply | Reply with quote | Quote
+3#vinay sagar s.d2014-02-07 20:18
try this for names having first name, mid name, last name...

=LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1),1000)),FIND(" ",TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1),1000)),1)-1)


where A1 is the cell in which the name is located....
Reply | Reply with quote | Quote
0#K R2014-02-17 04:50
Dear # vinay sagar s.d

Its not working out to split the name contains first name, mid name, last name.
Can u please help me to split the same for the below given names.
With space & without space.

My constraint for the another category is can we split the names my upper case.

E.g (1): William J Smith
E.g (2): WilliamJSmith
Reply | Reply with quote | Quote
+1#Manish Gupta2014-04-08 12:55
Hi,
I was trying to use your formula for the change of first, middle and last name in three different- different column of One complete name but I did not get correct answer of this questions, So please resolve this problem and sent me on my e-mail id.

(Note:- My e-mail id is "")
Reply | Reply with quote | Quote
+3#vinay sagar2014-02-24 04:49
Dear #K R

i think writing formula for E.g(2)is imposible even for Mocrosoft people... however i can give you the formula for E.g(2).. go for it

(1).First name
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),1,LEN(A1)))

(2).Mid Name
=IFERROR(IFERROR(LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1),1000)),FIND(" ",TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1),1000)),1)-1),RIGHT(A1,LEN(A1)-FIND(" ",A1,1))),A1)

(2).Last name
=IFERROR(RIGHT(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),LEN(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))-FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),1)),IFERROR(IFERROR(LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1),1000)),FIND(" ",TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1),1000)),1)-1),RIGHT(A1,LEN(A1)-FIND(" ",A1,1))),A1))

A1 is the cell address where your name is located....
Plz note that dont copy and past the above formula, because while copy and pasting it generates some "space" in-between formulas,,, thus it will throw error... so its better to type the above formula self as-it-is...
** Even this formulas are not working, kindly give your Email ID so that i can send the excel sheet with formula,, or mail me at
Reply | Reply with quote | Quote
0#hudson2014-08-08 07:22
are you the guy who developed excel? that formula is crazy man..!!
Reply | Reply with quote | Quote
0#Vinay Sagar2014-03-19 12:06
Dear #K R
(After correcting mistake in typing)

i think writing formula for E.g(2)is imposible even for Mocrosoft people... however i can give you the formula for E.g(1).. go for it

(1).First name
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),1,LEN(A1)))

(2).Mid Name
=IFERROR(IFERROR(LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN (A1),1000)),FIN D(" ",TRIM(MID(SUBS TITUTE(A1," ",REPT(" ",LEN(A1))),LEN (A1),1000)),1)- 1),RIGHT(A1,LEN (A1)-FIND(" ",A1,1))),A1)

(2).Last name
=IFERROR(RIGHT(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),LEN(RI GHT(A1,LEN(A1)- FIND(" ",A1,1)))-FIND( " ",RIGHT(A1,LEN( A1)-FIND(" ",A1,1)),1)),IF ERROR(IFERROR(L EFT(TRIM(MID(SU BSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN (A1),1000)),FIN D(" ",TRIM(MID(SUBS TITUTE(A1," ",REPT(" ",LEN(A1))),LEN (A1),1000)),1)- 1),RIGHT(A1,LEN (A1)-FIND(" ",A1,1))),A1))

A1 is the cell address where your name is located....
Plz note that dont copy and past the above formula, because while copy and pasting it generates some "space" in-between formulas,,, thus it will throw error... so its better to type the above formula self as-it-is...
** Even this formulas are not working, kindly give your Email ID so that i can send the excel sheet with formula,, or mail me at
Reply | Reply with quote | Quote
0#vinay sagar s d2014-04-09 18:48
# Manish Gupta

ok... Send me the sheet showing error to my e mail, so that i can correct all the errors... My id -

thanks..
Reply | Reply with quote | Quote
0#Ashis Kumar2014-04-21 19:56
Vinay is Psychotic & I like people who have lost it...

Your Great Fan!
Regards.
Reply | Reply with quote | Quote
0#Ravindra Tomar2014-05-01 10:07
Execellent!!! It works
Reply | Reply with quote | Quote
0#Arun2014-05-16 00:09
Very helpful, THanks .
Reply | Reply with quote | Quote
0#Smithg8742014-05-20 15:34
Wow, wonderful blog layout! How long have you been blogging for? you made blogging look easy. The overall look of your web site is excellent, as well as the content! kbkfbgdddeffebgd
Reply | Reply with quote | Quote
0#Jacqueline2014-06-26 04:20
Thank you thank you! Your tip on splitting names across two columns has just saved me a couple of hours of work on a very busy day. I really appreciate you sharing it. :lol:
Reply | Reply with quote | Quote
0#Lucille2014-06-27 06:00
My brother suggested I might like this blog. He was entirely right.

This post actually made my day. You cann't imagine just how much
time I had spent for this info! Thanks!

Also visit my blog post; fatih evden eve nakliyat
Reply | Reply with quote | Quote
+1#Erik2014-06-30 16:09
Very helpful. Thank you!
Reply | Reply with quote | Quote
0#vipin2014-07-08 09:37
i want to split first name and last name ,but the problem is there is no comma,no space in between them. so is there any solution for it.do reply fast as it is urgent
Reply | Reply with quote | Quote
+1#vinay sagar2014-07-09 01:53
(1).First name
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),1,LEN(A1)))

(2).Mid Name
=IFERROR(IFERROR(LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN (A1),1000)),FIN D(" ",TRIM(MID(SUBS TITUTE(A1," ",REPT(" ",LEN(A1))),LEN (A1),1000)),1)- 1),RIGHT(A1,LEN (A1)-FIND(" ",A1,1))),A1)

(2).Last name
=IFERROR(RIGHT(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),LEN(RI GHT(A1,LEN(A1)- FIND(" ",A1,1)))-FIND( " ",RIGHT(A1,LEN( A1)-FIND(" ",A1,1)),1)),IF ERROR(IFERROR(L EFT(TRIM(MID(SU BSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN (A1),1000)),FIN D(" ",TRIM(MID(SUBS TITUTE(A1," ",REPT(" ",LEN(A1))),LEN (A1),1000)),1)- 1),RIGHT(A1,LEN (A1)-FIND(" ",A1,1))),A1))

A1 is the cell address where your name is located....
Plz note that dont copy and past the above formula, because while copy and pasting it generates some "space" in-between formulas,,, thus it will throw error... so its better to type the above formula self as-it-is...
Reply | Reply with quote | Quote
0#vipin2014-07-12 09:11
not working, i only need the split into First name and lastname
Reply | Reply with quote | Quote
0#x010159182014-07-17 12:26
name:=LEFT(TRIM(A1);FIND(" ";TRIM(A1)))

middlename:=IFERROR(LEFT(TRIM(MID(TRIM(A1),FIND(" ",TRIM(A1)),50)),FIND(" ",TRIM(MID(TRIM(A1),FIND(" ",TRIM(A1)),50)))),"No Middle Name")

lastname:=RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND(" ",TRIM(A1))-IFERROR(FIND(" ",TRIM(MID(TRIM(A1),FIND(" ",TRIM(A1)),20))),0))
Reply | Reply with quote | Quote
0#Bransbilly2014-08-15 11:25
insert more columns to your right. then concatenante using this function =CONCATENATE(C7," ",D7," ",E7)

substitute where applicable to your cell.
" " will bring a space in between the names.
Reply | Reply with quote | Quote
0#Avnesh Chaudhary2014-08-20 16:09
plz tell me how to split first and last name if they are combined with any character other than letters. E.G.Avnesh.chaudhary
vishal1mittal
akash#sharma
Prateek_bansal
plz help me..plz
Reply | Reply with quote | Quote
0#vinay sagar2014-08-21 15:25
dear avnesh,, download this excel file containing formulas... hope it will help u...
http://www.4shared.com/file/zthYyoUBce/Book1.html
Reply | Reply with quote | Quote
0#ziyaultaj2014-08-24 16:04
I want to learn separate name from different columns lebels in a given range.
Reply | Reply with quote | Quote

Add comment


Security code
Refresh