Excel: split first and last names into separate columns

The tutorial shows how to separate first and last name in Excel with formulas or Text to Columns, and how to quickly split a column of names in various formats to first, last and middle name, salutations and suffixes.

It is a very common situation in Excel that your worksheet contains a column of full names, and you want to split first and last name into separate columns. The task can be accomplished in a few different ways - by using the Text to Columns feature, formulas, and Split Names tool. Below you will find full details on each technique.

How to split names in Excel with Text to Columns

In situations when you have a column of names of the same pattern, for example only first and last name, or first, middle and last name, the easiest way to split names into separate columns is this:

  1. Select the column of full names that you'd like to separate.
  2. Head to the Data tab > Data Tools group and click Text to Columns. Splitting names with the Text to Columns feature
  3. On the first step of the Convert Text to Columns Wizard, select the Delimited option and click Next. Select the Delimited option.
  4. On the next step, select one or more delimiters and click Next.

    In our case, different parts of names are separated with spaces, so we choose this delimiter. The Data preview section shows that all of our names are parsed just fine.

    Select one or more delimiters that separate different parts of names.

    Tip. If you are dealing with names separated with a comma and space like Anderson, Ronnie, then check the Comma and Space boxes under Delimiters, and select the Treat consecutive delimiters as one checkbox (usually selected by default).

  5. On the last step, you select the data format and destination, and click Finish.

    The default General format works nice in most cases. As the Destination, specify the topmost cell in the column where you want to output the results (please keep in mind that this will overwrite any existing data, so be sure to choose an empty column).

    Choose where to output the results.

Done! The first, middle, and last name are divided into separate columns:

The first, middle, and last name are split into separate columns.

Separate first and last name in Excel with formulas

As you have just seen, the Text to Columns feature is quick and easy. However, if you plan to make any changes to the original names and are looking for a dynamic solution that will update automatically, you'd better divide names with formulas.

How to split first and last name from full name with space

These formulas cover the most typical scenario when you have the first name and last name in one column separated by a single space character.

Formula to get first name

The first name can be easily extracted with this generic formula:

LEFT(cell, SEARCH(" ", cell) - 1)

You use the SEARCH or FIND function to get the position of the space character (" ") in a cell, from which you subtract 1 to exclude the space itself. This number is supplied to the LEFT function as the number of characters to be extracted, starting on the left side of the string.

Formula to get last name

The generic formula to extract a surname is this:

RIGHT(cell, LEN(cell) - SEARCH(" ", cell))

In this formula, you also use the SEARCH function to find the position of the space char, subtract that number from the total length of the string (returned by LEN), and get the RIGHT function to extract that many characters from the right side of the string.

With the full name in cell A2, the formulas go as follows:

Get the first name:

=LEFT(A2,SEARCH(" ",A2)-1)

Get the last name:

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))

You enter the formulas in cells B2 and C2, respectively, and drag the fill handle to copy the formulas down the columns. The result will look something similar to this:

Formulas to split first and last name in Excel

If some of the original names contain a middle name or middle initial, you'd need a bit more tricky formula to extract the last name:

=RIGHT(A2, LEN(A2) - SEARCH("#", SUBSTITUTE(A2," ", "#", LEN(A2) - LEN(SUBSTITUTE(A2, " ", "")))))

Here is a high-level explanation of the formula's logic: you replace the last space in the name with a hash sign (#) or any other character that do not appear in any name and work out the position of that char. After that, you subtract the above number from the total string length to get the length of the last name, and have the RIGHT function extract that many characters.

So, here's how you can separate the first name and surname in Excel when some of the original names include a middle name:

Formula to extract a surname when some of the original names include a middle name

How to separate first and last name from name with comma

If you have a column of names in the Last name, First name format, you can have them split into separate columns by using the following formulas.

Formula to extract first name

RIGHT(cell, LEN(cell) - SEARCH(" ", cell))

Like in the above example, you use the SEARCH function to determine the position of a space character, and then subtract it from the total string length to get the length of the first name. This number goes directly to the num_chars argument of the RIGHT function indicating how many characters to extract from the end of the string.

Formula to extract last name

LEFT(cell, SEARCH(" ", cell) - 2)

To get a surname, you use the LEFT SEARCH combination discussed in the previous example with the difference that you subtract 2 instead of 1 to account for two extra characters, a comma and a space.

With the full name in cell A2, the formulas take the following shape:

Get the first name:

=RIGHT(A2, LEN(A2) - SEARCH(" ", A2))

Get the last name:

=LEFT(A2, SEARCH(" ", A2) - 2)

The below screenshot shows the results:

Separating first and last name from full name with comma

How to split full name to first, last, and middle name

Splitting names that include a middle name or middle initial requires slightly different approaches, depending on the name format.

If your names are in the First name Middle name Last name format, the below formulas will work a treat:

A B C D
1 Full name First name Middle Name Last name
2 FirstName MiddleName LastName =LEFT(A2,SEARCH(" ", A2)-1) =MID(A2, SEARCH(" ", A2) + 1, SEARCH(" ", A2, SEARCH(" ", A2)+1) - SEARCH(" ", A2)-1) =RIGHT(A2,LEN(A2) - SEARCH(" ", A2, SEARCH(" ", A2,1)+1))
Result: David Mark White David Mark White

To get the first name, you use the already familiar LEFT SEARCH formula.

To get the last name, determine the position of the 2nd space by using nested SEARCH functions, subtract the position from the total string length, and get the length of the last name as the result. Then, you supply the above number to the RIGHT function instructing it to pull that number of characters from the end of the string.

To extract the middle name, you need to know the position of both spaces in the name. To determine the position of the first space, use a simple SEARCH(" ",A2) function, to which you add 1 to start the extraction with the next character. This number goes to the start_num argument of the MID function. To work out the length of the middle name, you subtract the position of the 1st space from the position of the 2nd space, subtract 1 from the result to get rid of a trailing space, and put this number in the num_chars argument of MID, telling it how many characters to extract.

Splitting full name to first, last, and middle name

And here are the formulas to separate names of the Last name, First name Middle name type:

A B C D
1 Full name First name Middle name Last Name
2 LastName, FirstName MiddleName =MID(A2, SEARCH(" ",A2) + 1, SEARCH(" ", A2, SEARCH(" ", A2) + 1) - SEARCH(" ", A2) -1) =RIGHT(A2, LEN(A2) - SEARCH(" ", A2, SEARCH(" ", A2, 1)+1)) =LEFT(A2, SEARCH(" ",A2,1)-2)
Result: White, David Mark David Mark White

A similar approach can be used to split names with suffixes:

A B C D
1 Full name First name Last name Suffix
2 FirstName LastName, Suffix =LEFT(A2, SEARCH(" ",A2)-1) =MID(A2, SEARCH(" ",A2) + 1, SEARCH(",",A2) - SEARCH(" ",A2)-1) =RIGHT(A2, LEN(A2) - SEARCH(" ", A2, SEARCH(" ",A2)+1))
Result: Robert Furlan, Jr. Robert Furlan Jr.

That's how you can split names in Excel by using different combinations of functions. To better understand and probably reverse-engineer the formulas, you are welcome to download our sample workbook to Separate Names in Excel.

Tip. In Excel 365, you can make use of the TEXTSPLIT function to separate names by any delimiter that you specify.

Separate name in Excel 2013, 2016 and 2019 with Flash Fill

Everyone knows that Excel's Flash Fill can quickly fill data of a specific pattern. But did you know that it can also split data? Here's how:

  1. Add a new column next to the column with the original names and type the name part that you want to extract in the first cell (the first name in this example).
  2. Start typing the first name in the second cell. If Excel senses a pattern (in most cases it does), it will populate the first names in all other cells automatically. Separating names with Excel's Flash Fill
  3. All you have to do now is to press the Enter key :)

Tip. Usually the Flash Fill feature is enabled by default. If it does not work in your Excel, click the Flash Fill button on the Data tab > Data tools group. If it still doesn't work, then go to File > Options, click Advanced, and make sure the Automatically Flash Fill box is selected under Editing options.

Split Names tool - fastest way to separate names in Excel

Plain or tricky, Text to Columns, Flash Fill and formulas work well only for homogeneous datasets where all names are of the same type. If you are dealing with different name formats, the above methods will mess up your worksheets by putting some name parts in wrong columns or returning errors, for example:

Errors when splitting names in Excel

In such situations, you can commit the work to our Split Names tool, which perfectly recognizes multi-part names, over 80 salutations and about 30 different suffixes, and works smoothly on all version of Excel 2016 to Excel 2007.

With our Ultimate Suite installed in your Excel, a column of names in various formats can be split in 2 easy steps:

  1. Select any cell containing a name you want to separate and click the Split Names icon on the Ablebits Data tab > Text group. Split Names tool
  2. Select the desired names parts (all of them in our case) at click Split. Select the name parts into which you want to split a full name.

Done! Different parts of names are spread out across several columns exactly as they should, and the column headers are added automatically for your convenience. No formulas, no fiddling with commas and spaces, no pain at all.

Different parts of names are spread out across several columns.

If you are curious to try the Split Names tool in your own worksheets, feel free to download an evaluation version of the Ultimate Suite for Excel.

Available downloads

Formulas to split names in Excel (.xlsx file)
Ultimate Suite 14-day fully-functional version (.exe file)

Table of contents

90 comments

  1. The sheet contains 3 columns: First Name | Middle Name | Last Name

    Data contains: First Name + Middle Name 1 + Middle Name 2 + Last name

    First Name formula: =LEFT(cell, SEARCH(" ", cell) - 1)
    Last Name Formula: =RIGHT(cell, LEN(cell) - SEARCH("#", SUBSTITUTE(cell," ", "#", LEN(cell) - LEN(SUBSTITUTE(cell, " ", "")))))

    What is the formula for Middle Name column?

    • Hi! To separate text into cells, you can use this guide: Extract Nth word from text string.
      You can also use the new TEXTSPLIT function. Look for the example formulas here: TEXTSPLIT function in Excel: split text strings by delimiter.
      I recommend paying attention to the Split Names tool. Split Names for Excel is a tool that can quickly clean up a worksheet with differently formatted names in the same column. It separates first, last, and middle names into individual columns. The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.

    • I need help on this. How can i separate the names from the dates with same column.

      Data: Like this format below, i can't send it in image. The data is with same column.

      **12123: Loyal, Pedro**
      2022-12-26
      2022-12-27
      2022-12-28

      • Hi! If your data is in a single cell, you can use TEXTSPLIT to split it into separate cells:

        =TEXTSPLIT(A1,,CHAR(10))

        You can find the examples and detailed instructions here: TEXTSPLIT function in Excel: split text strings by delimiter.
        If I understand your task correctly, our Split text tool may help you solve it in a few clicks.
        This tool lets you create multiple columns or rows from a single cell, whether you need to separate split cells with commas, spaces, hyphens or other characters.
        The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.

        • Hi, Alexander.

          Thanks for this but it did not work.

          the id and name and the dates in on column but on different row..

          For example same column but row1 **12123: Loyal, Pedro** then row 2 same column the series of dates .

  2. i am getting value error where user does not have any last name. pl guide how to relive.

  3. Hello, how can i split these columns, when he have in some situation

    Familyname, Givename 1, Givename 2.

    I need a table with 3 coloumns but empty cells when we don't have the Givenname 2.
    This is the example.

    For family name I used =LEFT(B2,FIND(" ",B2)-1) and it worked.

    But I don't know for the 2 Givennames.

    CĂPRAR ANA CEZARA
    CENTEA LAURA
    CENTEA MARA ELENA
    CHIPER IUSTINA COSMINA
    CIOCAN FELICIA ILINCA
    CIONT PATRICIA DANIELA
    CIUPEI FILIP

    • Hi! Pay attention to the following paragraph of the article above: How to split full name to first, last, and middle name. It covers your case completely.

      • Thank You, but we have the following situation: some people have 1 given name, and some people 2 given names. And in this case a cell must be empty. How can I solve this problem?

        ----------------------------------------------------------------------------
        Mocean Eric Mihai | Mocean Eric Mihai
        Pop Dana | Pop Dana

        • Hi! If you want to hide the error message and replace it with an empty cell, use the IFERROR function. For example:

          =IFERROR(RIGHT(A2,LEN(A2) - SEARCH(" ", A2, SEARCH(" ", A2,1)+1)),"")

          I hope it’ll be helpful.

  4. I have a spreadsheet with a Salutation Column, contains Mr. and Mrs. X, Dr. and Mrs. X, etc. Some cells are the husband and wife not separated by an "and". How do I find the cells that need the "and" added and what formula would I use?

    Ex. "Mr. Angel ArroyoMs. Christine Vieira" needs to changed to Mr. Angel Arroyo and Ms. Christine Viera

    How would I go about finding all the cells like this and changing them? List has 10k+ names

      • Hello,

        Thank you for your help. I'm having trouble selecting the column and applying that formula. I insert the column into the first cell and highlight until the end of the entries and it keeps giving me a #spill error. It also says I'm missing a closing parentheses.

  5. Hi!
    How do we separate these format in excel..
    ZERRUDO, SHANLEY ANGEL GEQUILLANA

  6. how to separate this name: MARY GRACE SANTOS BALTAZAR

    first name: MARY GRACE
    middle name: SANTOS
    last name: BALTAZA

  7. Need to split first name & middle name & last name

  8. I have a question, how would you separate the names based on their employee ID? for example, I have 2 sheets, the second sheet has the "master data" with the employee IDs and their names, but in the main spreadsheet, the employee ID's are not in the same order and I need to separate their names based on First and Last name but also based off their Employee ID.

  9. Thank you. Separating complex names with multiple middle names worked first time for me. Very nice work Svetlana. We stand with Ukraine.

  10. How do I separate full name in one column that has two name names, 3 name names and 3 names with suffix?

  11. THANKS FOR THE TIPS

  12. Hi

    How can I separate first name and last name for this style in a row [all are in a row] with formula:

    Hamed, Karimi; Hiva, Navai; Karim, Sodavi

    Thank you

    • Hi!
      First, split text into 3 cells by the separator ";". Then split each of these cells by the delimiter ",". All the necessary information is in the article above.

  13. Thank you - amazing! Worked fabulously!

  14. hello may i ask how will i separate this name Simon, Athena Jhane G. into 3 columns Last name, FIrst Name, Middle Initial. i am having a hard time separating with person who has 2 first name. Thank you.

  15. Thanks so much for your help!

  16. How to saperate each other Emloyee ID and name from one column?
    For example,

    A1.
    Employee ID Name
    100312-Mark-Brown

    I hope you understand my questions

  17. Awesome. But how can we delete the first column which includes the First Name and Last name? Because I only wanna see the separated columns?

  18. I like the presentation and descriptions.
    keep it up

  19. This is amazingly excellent...
    I have one more question though: How do you separate an entry name with: First Name, Middle Name, Last Name, & Name Extension;; like >> DAVID DUVALL SMITH JR...?

    Thank you

  20. How can i find i replace kumar

  21. Awesome! Really helped man!!

  22. Is it possible to create a cell that re-formats the name into an email address?

    For example, if I have a cell that contains Firstname Lastname, and I want the cell next to it to say Firstname.Lastname@company.com

  23. Thanks am very grateful the solution you provide works
    God bless you richely

  24. How i can give space between first and last name in excel name shown in my excel are like ARMANDOPOMAR.
    originally Name is ARMANDO POMAR. please help

  25. How do I handle a last name such as St. Clair? It is creating an extra column and won't go into the Last Name Column only?

    • Hello!
      First you need to strip the last name with prefix. When doing this, you use a space as a separator. Then separate the prefix from this result in a new column, using dot as a separator.
      We have a ready-made solution for your task. I'd recommend you to have a look at our Ablebits Data — Split Names. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  26. Hello, In the following example I have lists of many names taking 3 rows each ("C" is in row 1, "C" in row 2, "Kolias" in row 3. Rows 1-2-3 are merged on either side of the name. I would like result to be Cee Kolias in 1 merged cell to match the data on either side. Thank-you!

    C
    Cee
    Kolias

    • Hello!
      To combine the values of three cells, you can use a simple formula
      =A1&" "&A2&" "&A3

      However, I don't quite understand how you can get "Cee Kolias" from C, C, Kolias.
      Please describe your problem in more detail.

  27. What if in once cell there are multiple names? The sample is in once cell, I have 10 names of people? Thank you in advance!

  28. AshishMishra Who to divided Frist name or last name

  29. VERY USEFULL

  30. How to delimit name into first name and last name

  31. Looking for the formula that locates first name only from which contains only First name i.e. "Betty" OR contains First & Last Name "Betty Grace" OR contains First, Middle & Last Name as "Betty Grace Johnson". I only want "Betty"

    • =left(A2,Find(" ",A2,1)

  32. What if you have a name like Kofi Asante Asare and you want to split them into two.How do you go about it

    • Hello!
      Split full name to first, last, and middle name. How to do it - read above. Then combine the two names with &.

      =LEFT(A2,SEARCH(" ", A2)-1) & MID(A2, SEARCH(" ", A2) + 1, SEARCH(" ", A2, SEARCH(" ", A2)+1) - SEARCH(" ", A2)-1)

  33. Thank you very much
    Has been very helpful

  34. And if I receive the data where against some first name is provided and against some First + last name is provided.

    How can we split those using formula
    Sonia (if I use current formula then it will #value error)
    Divya Joseph
    Manali Shah

    • Hello!
      I’m sorry but your task is not entirely clear to me.
      For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result. It’ll help me understand it better and find a solution for you. Thank you.

    • I had the same problem and i found the solution by adding the Iferror condition in the beginning, So it will be like IfError(A2,Left(A2,Search(" ",A2)-1). this means if formaula in else part is giving error which it will give incase of only one name or name without space then take the A2 directly.

      Hope This Helps
      KD

  35. Excellent work....Inshallah your team get rewards for this work. Everyone should get inspiration from this and work well and share knowledge like this and as a noble cause not for commercial use.

  36. =IFERROR(LEFT(G2,FIND(" ",SUBSTITUTE(G2,","," "))-1),MID(G2,1,LEN(G2)))

  37. what if there is only a single name in the complete data. i.e. Rahul

  38. I'm having a problem getting a formula to extract information from a cell that contains both the surname and initials. The initials sometimes have spaces in between and sometimes they don't. The info in the cell looks as follows.
    E.G.
    Jordaan E B
    van Heerden JG
    van der Merwe Q V
    Jansen van Rensburg H J K

    I'm trying to find a formula that will convert it into a neater format as follows so i can import the information into a Database.
    E.G.
    EB Jordaan
    JG van Heerden
    QV van der Merwe
    HJK Jansen van Rensburg

  39. How to get middle names when the full name consists of more than 3 names e.g. 4 or 5 names
    How can all middle names between first and last name be combined into one column as middle name

    Example: Martha Arthur Luther McDonalds

  40. Very informative and useful study.

  41. I have a problem about my excel, the first name middle name and the last name are mixed up together, EX. hassanmohamedhaji so how do can i separate them easilly.

    thanks

  42. Desiree:
    If the "KL" are always the last two characters and where the data is in F7 then this should work:
    =RIGHT(F7,2)

  43. 2KL
    1/4KL
    2KL

    how to seperate this ? the KL

  44. Which formula I need to use when the name is like below

    Shiraj Ahmed Allabaksh Salagar
    Shiraj Ahmed Allabaksh Jallalludin Salagar

  45. SIR,

    IF THE CONDITION LIKE THIS

    CHIRANJIVI KUMAR SHAH
    MAYANK KUMAR
    RANJAN SINGH RAJPUT

    GIVE ONE FORMULAE FOR FINDING MIDDLE NAME

    • Lalit:
      I guess it depends on how many names you've got and the use you have for the middle name, but I would try first to use the Text-to-Columns approach. It's pretty fast and the end result is that each name is in a separate cell and you can do what you like with them at that point.
      Select the names, click the Text-to-Columns button on the ribbon.
      You'll see the data in that window and you should select the Delimited button click Next
      Choose the Space button, click Next
      Choose the General button click Finish.
      The middle name is in its own separate cell waiting for you.
      In the case of that second name that appears not to have a middle name, you'll have to cut and paste the "Kumar" part into the last column. You can do a LEFT and then a RIGHT LEN splitting formula, but I don't think that would be much quicker.
      However, if the name list is being lengthened frequently , you might want to use another approach.
      I found this technique on the web somewhere several years ago and it works great. So, kudos to whoever wrote it. Especially for the names which have no middle name or initial.
      Keep in mind this box will not allow the total length of some of these formulas to be on one line, but they are to be entered in your formula bar on one line. So, be careful how you copy and paste these.
      Where the complete name is in H44 enter this into the cell that will hold the first name:
      =IFERROR(LEFT(H44,FIND(" ",SUBSTITUTE(H44,","," "))-1),"")
      Next, in a cell where you want to hold the middle name enter: =IFERROR(MID(TRIM(SUBSTITUTE(H44,","," ")),FIND(" ",TRIM(SUBSTITUTE(H44,","," ")))+1,FIND(" ",MID(TRIM(SUBSTITUTE(H44,","," ")),FIND(" ",TRIM(SUBSTITUTE(H44,","," ")))+1,255))-1),"")
      Note that this formula works great even if there is no middle name. In that case it will return a blank.
      Lastly, in the cell that you want to hold the last name enter:
      =TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(H44,","," "))," ",REPT(" ",100)),100))
      If your data is in more rows than 100, replace the 100's with the last row number.
      Copy these down the three columns and you're the office hero - at least for the day.
      So, there you have it, two ways to split text in Excel.

      • It's was very helpfully.

      • Hello Doug, how would one parse the string if a person has multiple middle names? Thanks... trying to figure that one out atm : P

        • Hello!
          To split text that is 4 words into 4 columns, you can use these formulas:

          =LEFT(A1,SEARCH(" ",A1)-1)

          =MID(A1,SEARCH(" ",A1)+1, SEARCH(" ",A1,SEARCH(" ",A1)+1)-SEARCH(" ",A1))

          =MID(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-1- LEN(SUBSTITUTE(A1," ",""))),1)+1, FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)- LEN(SUBSTITUTE(A1," ",""))),1)- FIND("*",SUBSTITUTE(A1," ","*", LEN(A1)-1-LEN(SUBSTITUTE(A1," ",""))),1)-1)

          =RIGHT(A1,LEN(A1)- FIND("*",SUBSTITUTE(A1," ","*", LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

          We have a tool that can solve your task in a couple of clicks: Ablebits Data - Split text or Split Names.

          This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial

          • Thank you Alexander : )

            Super helfpful.

    • you can use if() and separate it on the basis of space between words but you have to count the space between words,
      you can use len()-len(substitute()) and then check...
      in most cases it work for me .

  46. how to you separate multiple names

    • =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))

  47. Excellent! Thanks...

  48. Thanks svetlana, for your effort.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)