Excel Tip > Excel Text, Editing and Format > Excel Text > Extract the first, middle and last name in Excel 2010

Extract the first, middle and last name in Excel 2010

Fb-Button

To extract the first, middle and last name we use the formulas "LEFT", "RIGHT", "MID", "LEN", and "SEARCH" in Excel.

LEFT: Returns the first character(s) in a text string based on the number of characters specified.

Syntax of "LEFT" function: =LEFT (text,[num_chars])

Example:Cell A2 contains the text "Mahesh Kumar Gupta"
=LEFT (A2, 6), function will return "Mahesh"

 

RIGHT:Return the last character(s) in a text string based on the number of characters specified.

Syntax of "RIGHT" function: =RIGHT (text, [num_chars])

Example:Cell A2 contains the text "Mahesh Kumar Gupta"
=RIGHT (A2, 5), function will return "GUPTA"

 

MID:Return a specific number of character(s) from a text string, starting at the position specified based on the number of characters specified.

Syntax of "MID" function: =MID (text,start_num,num_chars)

Example:Cell A2 contains the text "Mahesh Kumar Gupta"
=MID (A2, 8, 5), function will return "KUMAR"

 

LEN:Returns the number of characters in a text string.

Syntax of "LEN" function: =LEN (text)

Example:Cell A2 contains the text "Mahesh Kumar Gupta"
=LEN (A2), function will return 18

 

SEARCH:The SEARCH function returns the starting position of a text string which it locates from within the text string.

Syntax of "SEARCH" function: =SEARCH (find_text,within_text,[start_num])

Example:Cell A2 contains the text "Mahesh Kumar Gupta"
=SEARCH ("Kumar", A2, 1), function will return 8

 

How to separating the names in Excel through formula? 

To understand how you can extract the first, middle and last name from the text follow the below mentioned steps:

Example 1: We have a list of Names in Column “A” and we need to pick the First name from the list. We will write the "LEFT" function along with the "SEARCH" function.

 

img1

 

  • Select the Cell B2, write the formula =LEFT (A2, SEARCH (" ", A2)), function will return the first name from the cell A2.
  • To Copy the formula in all cells press the key “CTRL + C” and select the cell B3 to B6 and press the key “CTRL + V” on your keyboard.

 

img2

 

To pick the Middle name from the list. We write the “MID” function along with the “SEARCH” function.

  • Select the Cell C2, write the formula =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1)) it will return the middle name from the cell A2.
  • To Copy the formula in all cells press key “CTRL + C” and select the cell C3 to C6 and press key “CTRL + V”on your keyboard.

 

img3

 

To pick the Last name from the list. We we will use the “RIGHT” function along with the “SEARCH” and “LEN” function.

  • Select the Cell D2, write the formula =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2)+1))) It will return the last name from the cell A2.
  • To Copy the formula in all cells press key “CTRL + C” and select the cell D3 to D6 and press key “CTRL + V” on your keyboard.

 

img4

This is the way we can split names through formula in Microsoft Excel.

To know more find the below examples:-

 

Extract file name from a path

How to split a full address into 3 or more separate columns

 

 

 

 

 

Previous
Next

Comments

  1. HALEY

    I AM LOOKING FOR A FORMULA TO GRAB THE FIRST AND LAST NAME FOR THE EXAMPLE BELOW.

    123456789 - FIRST LAST - EXTRA

    I DO NOT NEED THE NUMBERS OR THE EXTRA. JUST THE first and last.

    Reply ↓
  3. Allen Atienza

    Hi can some one help me in this Conflict,
    I need to Get the 1st name, Middle name, And Last Name!
    I have a Formula for this. But the issue is this..
    The given name are
    1.Ruth Lee
    2.Justin Gomez
    3.Alex D. Narvaez
    4.Joanne A. Smith
    5.Julie Naval
    Issue: Some name have middle name, and the others don't have.
    what formula should I use? when there is no middle name it will be blank, but when it have a Middle name it get the middle name.
    Thanks 🙂

    Reply ↓
  4. Joseph

    Hi, I was wondering if I can split the first name, middle name, last name, street address, street name, city, state, and zip if they are all in one cell. For example
    Rosalie Ann Mullins 18794 Jamestown Circle Northville, Michigan 48167
    This would be all in cell a1. Thanks for your help. Joe...from Michigan.

    Reply ↓
  5. Joseph

    Hi, I was wondering if I can extract each first, middle, last, street no, street name, city, state, and zip if they are all in one cell? For example:
    Rosalie Ann Mullins 18796 Jamestown Circle Northville, Michigan 48168 (All in cell A1). Need to split them up. Thank you for your help....Joe from Michigan.

    Reply ↓
  7. Deepak

    Any one help to find out first name and also mid name if mid name more than 3 character
    Client Name
    LALITA
    GEETA DEVI NAYAK
    MEHARUN NISHA
    DIPA MANOJ
    PREETI SINGHAL
    meena devi swami
    RAJIYA BEGAM
    SHEHIDE
    TARAWATI
    BHATERI DEVI
    sheela devi
    JANKI DEVI
    SUNITA
    ALKA KANWAR
    JAITUN
    POOJA DEBI
    CHHOTI DEVI
    VIMLA DEVI
    manju devi tak
    MANJU
    MUNNI DEVI
    GEETA DEVI
    TULSI DEVI
    AILARAKHI
    MUMTAJ BIBI

    Reply ↓
  8. Vishal

    How to extract NAME ,LOCATION & ABS from the name SHANKAR BEEJ BHANDAR-JAIPUR-CHILLI ABS
    in different coloum ,Like SHANKAR BEEJ BHANDAR in one coloum,JAIPUR in another coloum & CHILLI ABS in another coloum

    Reply ↓
  9. Ali Ali Mohsin

    The Example quoted for extracting middle name is good. However, how about it the full name consisted of 4 names or 5 names, the quoted formulas will only give you the second name after space. How can one get all middle name i.e. names between first name and last name

    Reply ↓
  11. Deepak Maurya

    Hallo

    Please tell me that how can do the 3 characters in find last name
    such as: Deepak Kumar Maurya then find Last name means Maurya text find.
    So please you can tell me about this....!!!

    Reply ↓
  12. Jez Emin

    I need to extract the surname when there can be several name possibilities;

    eg;

    Mr G Right
    Mr R Tyre
    Mr B D Needle

    The Last Names I get with the formula you give are;
    Right
    Tyre
    D Needle

    How can I fix that ?

    Reply ↓
  13. ashishmishra

    how to remove space and country code from below data

    145 2145145 result 1452145145
    9987 954748 result 9987954748
    9945 45 4878 result 9945454878
    '91 9987 125445 result 9987125445

    Reply ↓
  14. ashishmishra

    Hi i need to extract number from below data

    'as12asd21mis45asdds2' Result 1221452
    '11111asdgsadhga1225' Result 111111225
    asafas1457 Result 1457

    Reply ↓
  16. ragz

    Hi All, i have a list of names Ex-

    CA
    PA-US-PA
    PA-US-PA
    PA-US-PA
    IN
    PA
    however, my friend is asking me to put one formula for all but the outcome should only be "US". in this case single words will remain same like CN,IN and for 3 words (PA-US-PA) the result should be only US.
    Pls help me with this i m nt able to put a logic here.

    Reply ↓
    • Admin

      Hi Ragini,

      As per requirement, you don't want to extract the names however you need to use the logical statement here which will check if single word, then return as it is otherwise look for "-US*" and return "US" as output in the cell. If this is the case, following is the formula will help you to retrieve expected output.

      =IF(COUNTIF(A1,"*-US-*")=1,"US",A1)

      We assumed you have data in column A from cell A1 to A6. Put the above formula in corresponding cell (B1 to down) and it will return what you looking for.

      Also, we'd recommend you to login on our official website for your simple or complicated Excel/VBA query and get instant solution for the same.

      Cheers!
      Site Admin

      Reply ↓
    • Admin

      Hi Samson,

      We assume that you have all emails like this.

      To extract left name "wendy", use the following formula --> "=LEFT("wendy.poulton@eskom.co.za",SEARCH(".","wendy.poulton@eskom.co.za",1)-1)"

      To extract the last name "poulton", use the following formula --> "=MID("wendy.poulton@eskom.co.za",SEARCH(".","wendy.poulton@eskom.co.za",1)+1,SEARCH("@","wendy.poulton@eskom.co.za",1)-SEARCH(".","wendy.poulton@eskom.co.za",1)-1)".

      You may find both formulas lengthy but you can replace the email id with the cell reference and it will extract the first and last name from email address.

      Also, we will request you to please visit www.excelforum.com in case you have any Excel/VBA query. You can ask our experts and get the instant solutions for your queries.

      Thanks,
      Site Admin

      Reply ↓
  18. Muhammad Idrees

    Hi,

    this formulae is used for three names.

    i have a requirements of 4 names. how to split them in 4 columns?

    for example, i have First Name, Second Name, Third Name and Forth Name in one column combined all together. i want to split them in four columns.

    Regards.

    Reply ↓
    • Admin

      Hi Muhammad,

      Assuming you have "First Second Third Fourth" in cell A1 and as per your request you want to split them as First, Second, Third & Fourth names in individual columns respectively so the formula would be..

      To extract
      First Name --> "=LEFT(A1,SEARCH(" ",A1,1)-1)"
      Second Name --> "=MID(A1,SEARCH(" ",A1,1)+1,(SEARCH(" ",A1,SEARCH(" ",A1,1)+1)-1)-SEARCH(" ",A1,1))"
      Third Name ---> "=MID(A1,SEARCH(" ",A1,SEARCH(" ",A1,1)+1)+1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,1)+1)+1)-SEARCH(" ",A1,SEARCH(" ",A1,1)+1)-1)"
      Fourth Name ---> =RIGHT(A1,LEN(A1)-SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,1)+1)+1))

      Hope this is what you were looking for.

      Also, for any simple or complicated query, please login on www.excelforum.com and ask our experts.

      Happy Learning,
      Site Admin

      Reply ↓
      • Ali Ali Mohsin

        How about making the second two names (2nd and 3rd) in one column as a middle name. How can this be achieved?

        Reply ↓
  20. Rutvij

    Just don't get in all this mess just simply copy paste the formula given below in formula bar:

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

    =IFERROR(MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1)),"")

    =IFERROR(REPLACE(A2,1,SEARCH("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))),""),"")

    Recommended only on 2010 and above

    Reply ↓
    • Admin

      Hi Manoj,

      We would request you to please provide the exact criteria to meet your expected result. So our experts can help you in achieving the same. 🙂

      Thanks in advance!

      Regards,
      Team Excel Tip & Excel Forum

      Reply ↓
  22. Brian Godia

    Please help. I need to retrieve the middle name with the first character and the last character being in upper case. for instance retrieve "BillY" from broncho billy anderson

    Reply ↓
    • Admin

      Hi Brian,

      We are glad to assist you. Also, we would request you if you have any simple or complicated query, please visit Excel Forum and ask your query to our expert. You will get the solution to your queries in very less time.

      Coming back to your query asked by you above, we assume you have "broncho billy anderson" in cell A2. Enter the following formula in B2 to get the desired result as "BillY". 🙂

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

      Let us know if it helps you to meet your requirement.

      If you like the solution, we request you to visit our Facebook page and like us. 🙂

      Best Regards,
      Team Excel Tip & Excel Forum

      Reply ↓
  23. Tony Clemenger

    HELP please: GrahamMaurice - how to obtain two columns = Maurice and the other = Surname = Graham?

    Thinking this is simple excel; but I am simple!; best Tony Clemenger;

    and I have thousands of them:

    CiciullaSerge
    CullumCoral
    HookeyChad
    RissonGarry
    MelvilleScott
    WilliamsRodney
    MunroFiona
    CraftJamie
    BergincPeter
    RowlandsScott
    KoumidesMel
    RavenMark
    McGurganJustin
    SymonsRycki
    KellerEdward

    CAN HELP???

    Reply ↓
    • Ninad More

      HI Tony,

      Here is a formula which can help you split First name and Last name wherein the ONLY delimiter is an Uppercase. This should work on major of the names given in your example list apart from the name like "McGurganJustin" which has 3 uppercase.

      Enter the following formula in the designated cells; Considering A1 contains the name: CiciullaSerge

      C1 (Last Name) : =MID(A1,MATCH(1,(CODE(MID(A1,ROW($1:$255),1))>=65)*(CODE(MID(A1,ROW($2:$255),1))<90),)+1,255)

      PLEASE NOTE: This is an array formula (press CTRL+SHIFT+ENTER)

      B1 (First Name) : =SUBSTITUTE(A1,C1,"")

      Reply ↓
  24. clarkine

    I have a problem extracting compound given names and single names, as well as their middle initial in this order
    Larry, Martin Luther S.
    Hudson, Mary L.
    I used the formula =LEFT(MID(A6,FIND(" ",A6)+1,LEN(A6)),FIND(" ",MID(A6,FIND(" ",A6)+1,LEN(A6)))-1) for first name. OUTPUT was Martin only, I need to add Luther but for Hudson, Mary works just fine,
    In the middle initial I used the formula =RIGHT(A23,LEN(A23)-FIND(" ",A23,FIND(" ",A23,FIND(" ",A23)+2))), for Larry, Martin Luther S, output sa Luther S. how can i eliminate Luther but for Hudson, Mary L works just fine,, what should I do? thank you,

    Reply ↓
  27. Kevin O'Neill

    Use Flash Fill, new in Excel 2013, to fill out data based on an example. Flash Fill typically starts working when it recognizes a pattern in your data, and works best when your data has some consistency.

    Reply ↓
  30. Avnesh chaudhary

    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 ↓
    • Nisha Dhawan

      To extract the last name you can use below mentioned formula
      =IF (ISNUMBER (FIND (",",A2)),RIGHT(A2,LEN(A2)-FIND(",",A2)-1),A2)

      To extract the first name use this formula =LEFT (A2, SEARCH ("@", A2)-1).

      Reply ↓
        • Nisha Dhawan

          To extract the name from right you can use below mentioned formula

          =IF (ISNUMBER (FIND (“,”,A2)),RIGHT(A2,LEN(A2)-FIND(“,”,A2)-1),A2)

          For Example:-
          Column A Column B
          Names_______________First Names
          Bush, George________George
          Seinfeld, Jerry_____Jerry
          Jordan, Michael_____Michael
          Laura_______________Laura
          Kate________________Kate

          To extract the name from left use this formula =LEFT (A2, SEARCH (“@”, A2)-1)

          For Example:-
          Column A Column B
          Names_______________First Names
          type1@gmail.com________type1
          type12@gmail.com_____type12
          type123@gmail.com_____type123
          type1234@gmail.com_____type1234

          Reply ↓
      • Michael (Micky) Avidan

        @Nisha Dahawan,
        To the best of my knowledge your, suggested, formula does not extract the Last(!) name.
        Please recheck.
        Michael (Micky) Avidan
        “Microsoft® Answers” – Wiki author & Forums Moderator
        “Microsoft®” MVP – Excel (2009-2014)
        ISRAEL

        Reply ↓
  31. Michael (micky) Avidan

    By the Way - if I'm not mistaken the same can be accomplished with the feature: "Text to Columns".

    Michael (Micky) Avidan
    “Microsoft® Answers” – Wiki author & Forums Moderator
    “Microsoft®” MVP – Excel (2009-2014)
    ISRAEL

    Reply ↓
  32. Michael (micky) Avidan

    In order to extract the last name you may try a shorter formula.

    =TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",255)),255))

    Michael (Micky) Avidan
    “Microsoft® Answers" - Wiki author & Forums Moderator
    “Microsoft®” MVP – Excel (2009-2014)
    ISRAEL

    Reply ↓
  33. Mike

    "Try this to find 'Smith Jr.'
    =IF(ISERROR(SEARCH("" "",A2,SEARCH("" "",A2)+1)),RIGHT(A2,LEN(A2)-SEARCH("" "",A2)),RIGHT(A2,LEN(A2)-SEARCH("" "",A2,SEARCH("" "",A2)+1)))"

    Reply ↓

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Excel Tips

Categories

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.