String parse & extract formula (XP)

Topic

New Reply

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?

Reply | Quote

Replies

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

=MID(A1,IF(ISERROR(FIND(" ",A1,FIND(" ",A1)+1)),FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1))+1,100)

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.

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

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.

Reply | Quote

The workbook attached to post 696,748 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.

Reply | Quote