Results 1 to 9 of 9

Extracting middle names and last names

This is a discussion on Extracting middle names and last names within the Excel Questions forums, part of the Question Forums category; Hi, I have seen many different ways to extract middle names and last names. Some are really confusing to figure ...

  1. #1
    Board Regular
    Join Date
    Mar 2009
    Posts
    1,089

    Default Extracting middle names and last names

    Hi,

    I have seen many different ways to extract middle names and last names. Some are really confusing to figure out.

    Some people use, find and others use search to find a pattern occurring.

    What is the best and easiest way/formula to use to extract middle and last names. I guess the first name is pretty easy as everyone seems to extract the first name using the same formula. What if there is 1,2 or more middle names...


    i,e

    STEVEN JOHN PAUL GERRARD
    MICHAEL JOHN H ANTHONY SMITH
    SHAUN H NORMAN
    ROGER CLARK

    What is the best method/easiest formula to extract any middle names and last names. Can you please give examples of different formulas.


    What's the difference between the search and find?

    gracias


  2. #2
    Board Regular Akashwani's Avatar
    Join Date
    Mar 2009
    Posts
    2,894

    Default Re: Extracting middle names and last names

    A reply is not only helpful to others, but polite to those who have provided a solution!!

    To post sample data go here....
    HtmlMaker
    Excel Genie
    Use Borders

    Upload a Sample File


    Visit here for some EXCELlent videos...

  3. #3
    Board Regular
    Join Date
    Jan 2010
    Posts
    51

    Default Re: Extracting middle names and last names

    hi,

    the trick here is to find the position of the spaces in the name. you can use the search function to do. =search(findtext,within text,[start num]

    after you find the position of the first space, then you find the position of the second space, by using the [start num] part of the function,

    eg

    myen dushern pather
    search(" ",A1) will give me and answer = 5
    then i use search(" ",A1,6) will give me = 13
    i would also use len function to find the entire length of the name.
    to get the middle name you could use =mid(A1,6,(13-5) will give dushern
    mid function allows to retrive text from a cell from a specific position amongst the text in the cell for a specfic number of characters. its better to under stand the solution so that you can use it in other cases.

    later guy
    myen pather

  4. #4
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    11,023

    Default Re: Extracting middle names and last names

    mahmed1,

    Sheet1

     ABCD
    1Full NameFirstMiddleLast
    2STEVEN JOHN PAUL GERRARDSTEVENJOHN PAULGERRARD
    3MICHAEL JOHN H ANTHONY SMITHMICHAELJOHN H ANTHONYSMITH
    4SHAUN H NORMANSHAUNHNORMAN
    5ROGER CLARKROGER CLARK
    6    

    Spreadsheet Formulas
    CellFormula
    B2=LEFT(A2,FIND(" ",A2,1)-1)
    C2=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,"",MID(A2,FIND(" ",A2,1)+1,FIND(MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255),A2,1)-2-FIND(" ",A2,1)))
    D2=MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255)


    Excel tables to the web >> Excel Jeanie HTML 4




    The formula in cell B2 copied down:
    =LEFT(A2,FIND(" ",A2,1)-1)


    The formula in cell C2 copied down:
    =IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,"",MID(A2,FIND(" ",A2,1)+1,FIND(MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255),A2,1)-2-FIND(" ",A2,1)))


    The formula in cell D2 copied down:
    =MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255)
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007

  5. #5
    New Member
    Join Date
    Feb 2010
    Posts
    8

    Default Re: Extracting middle names and last names

    hey thanks hiker. This is a handy reference. Ill use it in the future.

  6. #6
    Board Regular
    Join Date
    Mar 2009
    Posts
    1,089

    Default Re: Extracting middle names and last names

    Quote Originally Posted by hiker95 View Post
    mahmed1,

    Sheet1

    ABCD
    1Full NameFirstMiddleLast
    2STEVEN JOHN PAUL GERRARDSTEVENJOHN PAULGERRARD
    3MICHAEL JOHN H ANTHONY SMITHMICHAELJOHN H ANTHONYSMITH
    4SHAUN H NORMANSHAUNHNORMAN
    5ROGER CLARKROGER CLARK
    6

    Spreadsheet Formulas
    CellFormula
    B2=LEFT(A2,FIND(" ",A2,1)-1)
    C2=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,"",MID(A2,FIND(" ",A2,1)+1,FIND(MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255),A2,1)-2-FIND(" ",A2,1)))
    D2=MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255)


    Excel tables to the web >> Excel Jeanie HTML 4




    The formula in cell B2 copied down:
    =LEFT(A2,FIND(" ",A2,1)-1)


    The formula in cell C2 copied down:
    =IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,"",MID(A2,FIND(" ",A2,1)+1,FIND(MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255),A2,1)-2-FIND(" ",A2,1)))


    The formula in cell D2 copied down:
    =MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255)

    Thank you so much for this formula

    Can you please explain how the formula works as i am a fairly new learner in Excel.

    Much appreciated

  7. #7
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    11,023

    Default Re: Extracting middle names and last names

    mahmed1,

    Can you please explain how the formula works as i am a fairly new learner in Excel.
    The formula in column B:
    =LEFT(A2,FIND(" ",A2,1)-1)

    Finds the first "space", and returns the LEFT part of the string in cell A2, for the the number of characters up to, and not including the "space".



    The formula in column D:
    =MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255)

    was a formula from another post on MrExcel. I think that it finds the last "space" character in cell A2, and returns the MID part of the string from the next character past the last "space" for the LEN (length) of the string minus the last character position of the last "space".



    The formula in column C:
    =IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,"",MID(A2,FIND(" ",A2,1)+1,FIND(MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255),A2,1)-2-FIND(" ",A2,1)))


    First counts how many "space" characters there are in the string, and if these is only "1 space" character, then there is no middle name, and returns a blank cell.

    If there are more than "1 space" characters in cell A2, then we use MID to return the string from the starting position + 1 of the first "space" character, to the "last space" character position - 1.
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007

  8. #8
    Board Regular jeffreybrown's Avatar
    Join Date
    Jul 2004
    Location
    San Antonio, Texas
    Posts
    4,379

    Default Re: Extracting middle names and last names

    mahmed1,

    In case you are interested in another reference...

    http://www.ozgrid.com/Excel/TextFormulas.htm
    or
    http://www.cpearson.com/excel/FirstLast.htm
    Last edited by jeffreybrown; Feb 20th, 2010 at 10:52 AM.
    Jeff

  9. #9
    Board Regular
    Join Date
    Mar 2009
    Posts
    1,089

    Default Re: Extracting middle names and last names

    Thank you all for your help

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com