PDA

View Full Version : Parsing Name Help



mzsuga
05-13-2010, 08:33 AM
I have a file with Names that are not parse.

The different examples are as such:

Victor Chu
Victor and Anna Chu
Victor F. Chu
Victor&Anna Chu
Victor Chu Jr.
Victor Chu Jr


So far I get the first name using:

=IF(ISNUMBER(FIND(" ",TRIM(A1))),LEFT(TRIM(A1),LOOKUP(32768,FIND(" ",TRIM(A1),ROW(INDIRECT("1:"&LEN(TRIM(A1))))))),TRIM(A1))

This will take everything except the last word in the text which is the last name.

But the problem is when there is a suffix like Jr. So the first name in that formula would be Victor Chu and the last name would be Jr.

But instead I want the first name to be Victor and the last name to be Chu Jr.

I have the last name as =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

Is there a way if there is a suffix, JR, JR., SR, SR., then for the first name take everything but the last 2 words since the last 2 is the last name and suffix but if there is no suffix then just take everything but the last word

Then if there is a suffix in the last name then take the last 2 word in the text but if there isnt then just take the last word?

Bob Phillips
05-13-2010, 10:32 AM
You can try these,

B1: =LEFT(A1,FIND(CHAR(1),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," Jr.","")," Jr","")," ",CHAR(1),LEN(SUBSTITUTE(SUBSTITUTE(A1," Jr.","")," Jr",""))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," Jr.","")," Jr","")," ",""))))-1)
C1: =SUBSTITUTE(A1,B1&" ","")

although I would use a helper column, like so

D1: =SUBSTITUTE(SUBSTITUTE(A1," Jr.","")," Jr","")
B1: =LEFT(A1,FIND(CHAR(1),SUBSTITUTE(D1," ",CHAR(1),LEN(D1)-LEN(SUBSTITUTE(D1," ",""))))-1)
C1: =SUBSTITUTE(A1,B1&" ","")