Extract the first, middle and last name in Excel 2010

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

 

 

 

 

 

Users are saying about us...

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

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

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

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

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

        • 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

      • 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

  5. For getting last name : use

    =MID(A2,LOOKUP(1,–((MID(A2,ROW(INDIRECT(“1:” &LEN(A2))),1))=” “),ROW(INDIRECT(“1:” &LEN(A2)))),50)

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

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

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

    • 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,"")

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

    • 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

    • 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

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

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

    • 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 http://www.excelforum.com and ask our experts.

      Happy Learning,
      Site 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 http://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

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

    • 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

  13. Hi i need to extract number from below data

    ‘as12asd21mis45asdds2′ Result 1221452
    ’11111asdgsadhga1225′ Result 111111225
    asafas1457 Result 1457

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

Leave a Reply to Nisha Dhawan Cancel reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube