Extracting Last Name and First Initial
 

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(",",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

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

"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

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

"Peo Sjoblom" wrote:

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

"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

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