Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|