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