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!