Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    String parse & extract formula (XP)

    I searched for the answer but did not find what I was looking for so I apologize it it has been previously addressed.

    I am having trouble with a formula. I have a spreadsheet with a column of names many names are in the format of FirstName LastName but some are in the format of FirstName MiddleName LastName.

    When I use the formaula =right(a1,len(a1)-find(" ",a1)) to extract the LastName, it works well for those names with the FirstName LastName format. However, it does not work when it encounters the FirstName MiddleName LastName format.

    Is there anyway to modify that formula to look for the space (" ") from the right instead of from the left? Also, sometimes there are four names and I may need to extract the last two. So, in addition to the basic question above, is there a rule or construct to follow that can be adapted to whatever is needed for text extraction?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: String parse & extract formula (XP)

    If the name will only consist of two parts (first name - last name) or of three parts (first name - middle name or initial - last name), you can use
    <code>
    =MID(A1,IF(ISERROR(FIND(" ",A1,FIND(" ",A1)+1)),FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1))+1,100)
    </code>
    to extract the last name from A1. It becomes more complicated if you also have names such as Richard Williams Jr. or Henry Everett Lansing III.

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: String parse & extract formula (XP)

    Hi Don,

    Whichever way you go, you're going to have problems when a person's surname consist of two or more words ...
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: String parse & extract formula (XP)

    Thanks for the input. I realize it can be complicated. The names I am working with are Asian names, some of which have been 'westernized' to two names while others remain as three or four names. None of them are compound names, though.

    Hans - your formula, of course, works well but I sure cannot understand all the nested FINDs. I have tried to parse them out to understand how it works but I fail. I also downloaded a Spreadsheet you provided to another user showing how to extract double last names using a similar, but different formula... or at least I think that was the intent of your example. I don't understand how the two formulas differ in approach, though.

    Paul - I agree with the names being problematic. I wonder how difficult it is to create a function that can (if I understand functions correctly) be used as part of a formula so the formula is not so long and complex.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: String parse & extract formula (XP)

    The workbook attached to <post:=696,748>post 696,748</post:> is intended to deal with names of the form Bill Clinton or George W. Bush - a first name and a last name, or a first name, a middle initial (followed by a period) and a last name.
    I used intermediate formulas before I created the monster formula from my previous reply. The attached workbook shows these intermediate formulas. Creating the monster formula involved successively substituting the intermediate formulas back into the end result. It also shows an example where the formula doesn't return the correct result.

    It would be possible to create a custom VBA function for this:

    Public Function GetLastName(AName) As String
    Dim intPos As Integer
    intPos = InStrRev(AName, " ")
    GetLastName = Mid(AName, intPos + 1)
    End Function

    Use like this:

    =GetLastName(A2)

    or if you store the function in a module in your Personal.xls macro workbook:

    =Personal.xls!GetLastName(A2)

    The sample workbook also demonstrates the use of this function.
    Attached Files Attached Files

Posting Permissions

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