Extracting Last Name and First Initial

  • Thread starter Thread starter Ruan
  • Start date Start date
R

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
 
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)
 
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
 
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
 
Thanks,

Both formulas work great.



Frank Kabel said:
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)
Click to expand...
 
Back
Top