Note: The other languages of the website are Google-translated. Back to English
English English

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, middle name column and last name column as the following screenshot shown. Here are some tricky methods to help you solve this problem:


Split full name to first and last name with formulas

Split full names to first and last names:

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:

1. Enter the formula of =LEFT(A2,FIND(" ",A2,1)-1) in a blank cell, says Cell C2 in this case, and then drag the fill handle down to the cells which you want to fill this formula, and all first names have been extracted at once, see screenshot:

doc split full names 2

2. Then enter the formula of =RIGHT(A2,LEN(A2)-FIND(" ",A2,1)) in another blank cell, Cell D2, and drag the fill handle down to the cells which you want to apply this formula, and all last names have been extracted in column D as following screenshot shown:

doc split full names 3


Split full names to first, middle and last names:

If you want to split full names to first, middle and last names into separate columns, the following formulas may do you a favor.

1. Enter this formula: =LEFT(A2,SEARCH(" ",A2)) into a blank cell C2, and then drag the fill handle down to the cells to apply this formula, and all first names have been split into column C, see screenshot:

doc split full names 4

2. Then enter this formula: =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1)) into a blank cell D2, and then drag the fill handle down to the cells to apply this formula, and all middle names have been split into column D, see screenshot:

doc split full names 5

3. At last, enter this formula: =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2)+1))) into a blank cell E2, and then drag the fill handle down to the cells to apply this formula, and all first names have been split into column E, see screenshot:

doc split full names 6


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

1. Select the column that you will split, in this case it is A2:A13.

2. Click the Text to Columns button under Data Tab.

doc split full names 7

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

doc split full names 8

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

doc split full names 9

5. In the following wizard, check the Text option in the Column data format section, and specify the Destination cell where you want to put the results. See screenshot:

doc split full names 10

6. Click the Finish button. Then you will see the full name column has been separated into first name column, middle name column  and last name column at once.


Split full name to first and last name with Kutools for Excel quickly and easily

If you are tired with the above two methods, here, I can recommend you a useful tool – Kutools for Excel, with its Split Names feature, you can quickly split the full names to first and last names, or split full names to first, middle and last names as you need.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. 

After installing Kutools for Excel, please do as follows:

1. Select the name range that you want to split.

2. Click Kutools > Merge & Split > Split Names, in the Split Names dialog box, specify the Split types you want, if you want to split the full name to first and last name, please check First name and Last name, see screenshot:

3. Then click OK button, and another dialog will appear, please select a cell where you want to put the result in the dialog, see screenshot:

doc split full names 12

4. And click OK button, the full names have been split into first name column and last name column, see screenshot:

doc split full names 13

Note: If your full name contains first, middle and last names needed to be split into three columns separately, you just need to check First name, Middle name and Last name in the Split Names dialog box and you will get the following result:

doc split full names 14

Download and free trial Kutools for Excel Now !


Split full names to first and last names by comma or other delimiters:

If your full names are separated by comma or other delimiters in cell,Kutools for Excel' Split Cells feature also can do you a favor, see screenshot:

doc split full names 15

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!


Split full name to first and last name with Kutools for Excel quickly

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (88)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
awesum ... did help alot ... thxxx
This comment was minimized by the moderator on the site
[quote]try this for names having first name, mid name, last name... [quote]how to split two words in one cell,,,,,,,,,,,,,,,,,By vinay sagar s.d[/quote] where A1 is the cell in which the name is located....By hari[/quote]e]
This comment was minimized by the moderator on the site
dear please solve this micro
This comment was minimized by the moderator on the site
Life Saver!! Thanks for the Demo!!
This comment was minimized by the moderator on the site
this only works if the people on your list don't have middle names! ahh so frustrating
This comment was minimized by the moderator on the site
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....
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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 "manishgupta.gupta884@gmail.com")
This comment was minimized by the moderator on the site
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 vinaysagarsd@gmail.com
This comment was minimized by the moderator on the site
are you the guy who developed excel? that formula is crazy man..!!
This comment was minimized by the moderator on the site
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 vinaysagarsd@gmail.com
This comment was minimized by the moderator on the site
Kindly Send me excel sheet with Formula. Regards Ranjan Kumar
This comment was minimized by the moderator on the site
# Manish Gupta ok... Send me the sheet showing error to my e mail, so that i can correct all the errors... My id - vinaysagarsd@gmail.com thanks..
This comment was minimized by the moderator on the site
Vinay is Psychotic & I like people who have lost it... Your Great Fan! Regards.
This comment was minimized by the moderator on the site
Execellent!!! It works
This comment was minimized by the moderator on the site
Very helpful, THanks .
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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:
This comment was minimized by the moderator on the site
Very helpful. Thank you!
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
(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...
This comment was minimized by the moderator on the site
not working, i only need the split into First name and lastname
This comment was minimized by the moderator on the site
Split of First and Last Name Alone Mike Joseph use Left & Find Formula to Extract First name =LEFT(A1,FIND(" ",A1)) To Extract Last Name use Right,len and Find Formula = =RIGHT(A1,LEN(A1)-FIND(" ",A1,1))
This comment was minimized by the moderator on the site
This formula worked perfectly for me... Any advice on how to remove comas? ex. St.Louis, I need it to be St. Louis. Thank you.
This comment was minimized by the moderator on the site
I tried the formula above but my data has everything it has first name Last name, first name middle initial last name and first name with a space "Le Roy". your formula works except for those with a middle initial so the last name is showing up for those as K Smith. is there a simpler formula to drop the K" " and not remove the first letter of the other last names? The first name worked great. thanks a lot
This comment was minimized by the moderator on the site
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))
This comment was minimized by the moderator on the site
graet function but if there is two midlle names it will only show one please could u send one which will show all middle names i.e. even if there is 3 midlle names thanks [i have one wich shows first and midlle e.g. name: Paul James John Smith (to show all besides last name is : =LEFT(A1,LEN(A1)-FIND(" ",A1,1)+1)]
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
I want to learn separate name from different columns lebels in a given range.
This comment was minimized by the moderator on the site
This saved me hours of work. THANK YOU!!!
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations