Comments on: Excel: split first and last names into separate columns

It is a very common situation in Excel that your worksheet contains a column of full names, and you want to split the first and last names into separate columns. The task can be accomplished in three different ways - by using the Text to Columns feature, formulas, and Split Names tool. Continue reading →

Comments page 2. Total comments: 93

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

  2. How can i find i replace kumar

  3. Awesome! Really helped man!!

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

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

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

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

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

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

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

  9. 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!

  10. AshishMishra Who to divided Frist name or last name

  11. VERY USEFULL

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

  13. 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"

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

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

    1. 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)

  15. Thank you very much
    Has been very helpful

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

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

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

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

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

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

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

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

  22. Very informative and useful study.

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

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

  25. 2KL
    1/4KL
    2KL

    how to seperate this ? the KL

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

    Shiraj Ahmed Allabaksh Salagar
    Shiraj Ahmed Allabaksh Jallalludin Salagar

  27. SIR,

    IF THE CONDITION LIKE THIS

    CHIRANJIVI KUMAR SHAH
    MAYANK KUMAR
    RANJAN SINGH RAJPUT

    GIVE ONE FORMULAE FOR FINDING MIDDLE NAME

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

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

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

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

          1. Thank you Alexander : )

            Super helfpful.

      2. It's was very helpfully.

  28. how to you separate multiple names

    1. =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))

  29. Excellent! Thanks...

  30. 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 :)