Thanks,

Both formulas work great.



"Frank Kabel" wrote in message
...
Hi
try
1. Last name:
=IF(ISNUMBER(FIND(",",A1)),TRIM(LEFT(A1,FIND(",",A 1)-1)),TRIM(LEFT(A1,FIND("
",A1)-1)))

2. Initial:
=LEFT(IF(ISNUMBER(FIND(",",A1)),TRIM(MID(A1,FIND(" ,",A1)+1,255)),TRIM(MID(A1,FIND("
",A1)+1,255))),1)

--
Regards
Frank Kabel
Frankfurt, Germany

Ruan wrote:
Hello,

I need to extract the Last Name and the First Initial of a person's
full name. The person's full name is written in this format: Last
Name, First Name.

Example:
Bond, James = Bond and J

However, sometimes the full name format isn't always as we require
it. We have come across the following scenarios -
1) Bond , James (error - space before the comma)
2) Bond,James (error - no space after the comma)
3) Bond, James (error - 2 spaces after the comma instead of one)
4) Bond James (error - no comma)

Does anyone know the formula I can use to always extract the Last
Name and the First Initial regardless if the full name format is
incorrect?
Some names might have Middle Initial and will be in this format -
Bond, James N.

Thanks
Ruan