Extracting Last Name and First Initial

[Ruan]

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

 

[Frank Kabel]

Hi
try
1. Last name:
=IF(ISNUMBER(FIND(",",A1)),TRIM(LEFT(A1,FIND(",",A1)-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)

 

[Guest]

Last Name

=TRIM(IF(ISERR(FIND(",",A1)),LEFT(A1,FIND(" ",A1)),LEFT(A1,FIND(",",A1)-1)))

First

=TRIM(IF(ISERR(FIND(",",A1)),MID(A1,FIND("
",A1),255),MID(A1,FIND(",",A1)+1,255)))

Regards,

Peo Sjoblom

 

[Guest]

Opps! Change the second formula to

=LEFT(TRIM(IF(ISERR(FIND(",",A1)),MID(A1,FIND("
",A1),255),MID(A1,FIND(",",A1)+1,255))))

Regards,

Peo Sjoblom

 

[Ruan]

Thanks,

Both formulas work great.

Hi
try
1. Last name:
=IF(ISNUMBER(FIND(",",A1)),TRIM(LEFT(A1,FIND(",",A1)-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)