Extract First and Last Name
Lynn,
Assumptions made:
The word "Claim" may or may not always be there.
Last Name, First Name will always be separated by a comma.
Name may have a middle initial (with or without a period).
With an entry in cell A1, formula for first name in B1 would be:
=MID(A1,FIND(",",A1)+2,IF(ISERR(FIND("Claim",A1)), LEN(A1),(FIND("Claim",A1)-2)-(FIND(",",A1)+1)))
Formula for last name in C1 would be:
=LEFT(A1,FIND(",",A1)-1)
Gobbledegook above explained........
Find the comma.
=FIND(",",A1)
Subtract 1 to get the length of the last name:
=FIND(",",A1)-1
Extract the last name:
=LEFT(A1,FIND(",",A1)-1)
With the above in mind, the first name will begin 2 spaces after the
comma.
=FIND(",",A1)+2
Find where the word "Claim" starts:
=FIND("Claim",A1)
hmmmm??? what if it;s not there?????
=IF(ISERR(FIND("Claim",A1)),"It's not there","It is there")
If it's not there, get the length of the entire string:
=LEN(A1)
If it is there, get the length of the string from 1 place to the right
of the comma to two places to the left of the word "Claim":
=FIND("Claim",A1)-2)-(FIND(",",A1)+1))
Now the fun part.........
Concatenate all the formulas together to make it work.
=MID(A1,FIND(",",A1)+2,IF(ISERR(FIND("Claim",A1)), LEN(A1),(FIND("Claim",A1)-2)-(FIND(",",A1)+1)))
John
Lynn wrote:
I have a column that contains a person's name and claimant
number. For example:
Doe, John Claim No. 230404
What I would like to do is extract the last and first
names into 2 separate columns. The name may or may not
have a claim no. and the claim numbers vary in length.
Thanks!
|