Split First and Last Names in Excel

If you have a list of names in Excel, with first and last names separated by a comma, you can use an Excel feature to split the first and last names into separate columns.

Select the List

To start, select the cells that contain the names.

TextToCol01

On the Ribbon in Excel 2007, click the Data tab, then click Text to Columns.

TextToCol02

If you're using an earlier version of Excel, click the Data menu, then click Text to Columns.

TextToCol07

The Convert Text To Columns Wizard

The Convert Text to Columns Wizard opens, and you can select the type of data that you want to convert.

Note: The wizard layout varies slightly in different versions of Excel, but the steps are the same.

  1. The list in this example uses a comma to separate the names, so select Delimited, then click Next.
    TextToCol03
  2. Next, add a check mark to the delimiter that's used in your text. You can select more than one, but in this example only Comma is required. We only want to split first and last names were the comma is inserted.
    TextToCol04
  3. Click Next, to go to Step 3.
  4. Select a destination cell, where you want the split text to start. The default setting is to overwrite the existing data, so if you want to keep it, select a different cell as the destination.
    TextToCol05
  5. Click Finish, and the data is split into separate columns.
    TextToCol06

Other Methods For Splitting Data

This method is quick and easy, and perfect for a one time event, such as importing a list of names, and splitting it.

However, if the names will change frequently, you could use a formula to split the names, instead of the Text to Columns feature. Then the first and last name columns will automatically update, if the full name changes.

Chip Pearson has sample formulas for Extracting First and Last Names, and VBA code for splitting data.

________________________

27 comments to Split First and Last Names in Excel

  • David

    I cannot tell you how often I have to deal with this. We have an application at work which dumps the names out like this, and when we try and turn them around for a mailing list, it has to be cleaned up.

    Just two notes for people that do this, however. One, consider carefully your source: watch out for multiple commas or "Company, Inc." Two, the space after the comma in the original text will be at the beginning of the cell after the text is separated. Separating the fields is important, but it is not the last step in cleaning up the data for use elsewhere.

  • Thanks David, that's good advice. Data cleanup can be a real challenge.

  • A great service you're providing here, Debra. Thank you.

    I have an EXCEL problem for you. I want to enter one of three channels (A,P,D) in A1 and a ZIP code in B1 and render the appropriate sales rep in C1.

    I won't have 99,999 ZIP rows. Instead, I'll know who covers ZIP 17100 to 17200 and so on.

    Some kind of Pivot table, I imagine. It's way over my level of expertise ... but probably just right for you.

    http://twitter.com/joehageonline

  • Thanks Joe, glad you like the blog.

    For your sales reps you could use a VLOOKUP formula, similar to the one shown here:
    http://www.contextures.com/xlFunctions02.html#Range

    Put the starting zip codes for each range in the first column and rep names in the second column. Then refer to that table in the VLOOKUP formula.

  • Since we're talking about splitting, here is a quick little VBA routine I wrote up to split names. It will split on any character, and can return the n'th result from the split.

    '-----------------------------------

    Function Split2(SourceString As String, SplitChar As String, Index As Long) As String

    Dim x() As String
    x = Split(SourceString, SplitChar)

    If UBound(x) < Index – 1 Then
    Split2 = ""
    Else
    Split2 = x(Index – 1)
    End If

    End Function
    '-----------------------------------

    Hope it's helpful.

    -John

  • [...] 2009.  Email This article to a Friend Debra Dalgleish at Contextures blog wrote an article Split First and Last Names in Excel. It gave me an idea about creating a formula that split the names and then also sort the names [...]

  • Zoey

    Hi Debra,
    Just wanted to thank you for the help you provided me today. I did a quick search to find how to switch the names and found your blog right away. The job was completed within minutes. You were a life saver!

  • Zoey, that's great! Thanks for letting me know that the instructions helped.

  • raven21

    hi there to splitDiff, i would like to ask how to use your codes? i cannot call it in excel.

  • Fuad

    Hi there,

    I need to extract the last word in a cell regardless the length of and position of it in a cell.

    It is like:

    Cell A1: Test Excel Office 2007
    Cell A2: Test Excel Office Functions 2007

    I want a formula to extract 2007 from both cells.

    Can anyone help. Would be grateful if you forward comments to hfuad@hotmail.com

    Thanks,

    Fuad

  • Rick Rothstein (MVP - Excel)

    @Fuad,

    This formula will return the last word in a string of text...

    =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

  • Rick Rothstein (MVP - Excel)

    @Fuad,

    Sorry, I meant to include the following before I sent my reply...

    As structured, the formula works for text of 99 characters or less... if your text can be longer, simply change both occurences of 99 to a number that is larger than your longest string of text.

  • Fuad

    Thanks Rick,

    That helps a lot

    Regards/Fuad

  • Stuart Krawll

    This formula will return the last word in a string of text...

    =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

    This works great! Is there any way to actually REMOVE the last name from the data in the cell. In other words, to split Tommy Lee Jones, into Tommy Lee in one field, and Jones in the next.

    Thanks, and respond to skrawll@beamstl.com. THANK YOU VERY MUCH

  • Rick Rothstein (MVP - Excel)

    @Stuart Krawl,

    First off, IMHO, when you ask a question in a public forum, you should not ask to have the answer emailed to you... people who read your question may want to see the answer also. Now, for your question... no, you cannot modify the data in a cell using a formula... you would need to use a macro like this one...

    Sub SeparateLastNameOut()
    Dim Cell As Range, NameParts() As String
    On Error Resume Next
    For Each Cell In Selection
    NameParts = Split(Cell)
    Cell.Offset(0, 1).Value = NameParts(UBound(NameParts))
    NameParts(UBound(NameParts)) = ""
    Cell.Value = Trim(Join(NameParts))
    Next
    On Error GoTo 0
    End Sub

    One note about identifying last names from text containing the full name... there is no foolproof method available to do it. I had a friend in work whose name was Joe Della Rossa... "Della Rossa" was his last name. The code above, the formula I posted previously and pretty much anyone else's attempt to identify this last name from within text containing his full name will fail.

  • Thanks Rick, that's great advice for Stuart. He can also subscribe to the comments for any article, and get an email if a new comment is posted. That's better than posting your email address publicly.

  • John Jacob

    Chip Pearson's (CP) formula works just fine for me until I get to the names with 2nd and 3rd names. Any ideas how I can fix names like these?

    Doe, John Smith F.
    Doe, John Smith Richard F.
    Doe, Ma. Jane Dianne F.

    Here's how they currently look like using CP's formula:

    Names are in Column A
    Column B/Column C/ Column D
    Doe/John/Smith F.
    Doe/John/Smith Richard F.
    Doe/Ma./Jane Dianne F.

    Here's what it should look like:

    Doe/John Smith/F.
    Doe/John Smith Richard/F.
    Doe/Ma. Jane Dianne/F.

    I tried working the formulas out but the complexity of it just boggles my mind.
    Any help from you guys? :)

  • Luis

    Debra your tutorials are awesome! I just need a little more help. I have a sheet in wich I have multiple strings on one cell separated by a coma and other more groups down in the same column B, I need them to be copied to another sheet and I already did all that, I have the code wich splits and copies but there are sometimes cells in blank in the same column. I just need to organize the data so it shows in the other sheet:

    Column B | ColumnC | ColumnD | ColumnE | Column F
    Group 1 | | Group 2 | | Group 3

    my code:
    Sub SplitTest()
    Dim arrText() As String
    Dim varItm As Variant
    Dim rngText As Range
    Dim rngCl As Range
    Dim i As Integer
    Dim j As Integer
    Set rngText = Sheet8.Range("B1000:B" & Range("B" & Rows.count).End(xlUp).row)
    j = -4
    For Each rngCl In rngText
    arrText = Split(rngCl, ",")
    i = 2
    For Each varItm In arrText '????
    Cells(i, j) = varItm
    i = i + 1
    Next varItm
    j = j + 1
    Next rngCl
    End Sub

    Thanks

  • [...] did some brainstorming, which we augmented with a list from the web. It was sliced and diced using Text to Columns and Remove Duplicates from the Excel 2010 Data [...]

  • Paula

    Thank you! You saved me hours of work.

  • Anna shore

    Ihave a spreadsheet with first and middle name in a cell with no space i.e. AnnaVictoria or JanetMay or DavidAnthony. Does any one know how I can split this into first name and second name in separate cells. I have been trawling for hours and cannot find anything. I need relatively easy steps to follow if possible. Many thanks in advance.

  • Girivasan

    Hi Please tel me the VBA code which will print the part of the sender name exported from outlook to excel
    For eg : sender name will be name1,name2 (name3).I want only name 2 to be exported to excel as a output.Kindly help

  • Martin

    Hi, "Stuart Krawll" thank you very much for the easy function, it helped alot.
    Just to add to your post, i used for the first name split.
    =TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

  • KATRAW

    i want to extract last name from string but i am unable kindly help.

    ex:
    KOSHALYA KAJAL D/O POONAM SUTHAR - i want SUTHAR
    KEVARAM PATEL S/O MEGHAJI PATEL - i want PATEL

    HOW CAN APPLY FORMULA?

  • darlene

    for each group of names - I have the first and last name in separate columns for the most part. I do see some of the group of names have the first name in the last name column and the last name in the first name column. The worksheet has over 200,000 grouped names - I am looking for a way to highlight the groups of names where the first name is in the last name column.

  • sanjay

    How to separate full name into 3 cols

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>