Hello,

I was able to extract the Last Name from a string, however I am having
difficulties extracting the First Name, especially when there is a Middle
Initial involved.

Example: Bond, James P.

I just need to extract "James".

Here is my formula I used for extracting the Last Name -
=UPPER(IF(ISNUMBER(FIND(",",A1)),TRIM(LEFT(A1,FIND (",",A1)-1)),TRIM(LEFT(A1,FIND("
",A1)-1))))


Thanks
Ruan

=MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1)

Vaya con Dios,
Chuck, CABGx3



"Ruan" wrote:

Hello,

I was able to extract the Last Name from a string, however I am having
difficulties extracting the First Name, especially when there is a Middle
Initial involved.

Example: Bond, James P.

I just need to extract "James".

Here is my formula I used for extracting the Last Name -
=UPPER(IF(ISNUMBER(FIND(",",A1)),TRIM(LEFT(A1,FIND (",",A1)-1)),TRIM(LEFT(A1,FIND("
",A1)-1))))


Thanks
Ruan

I am getting "#VALUE!" error.


"CLR" wrote in message
...
=MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1)

Vaya con Dios,
Chuck, CABGx3



"Ruan" wrote:

Hello,

I was able to extract the Last Name from a string, however I am having
difficulties extracting the First Name, especially when there is a Middle
Initial involved.

Example: Bond, James P.

I just need to extract "James".

Here is my formula I used for extracting the Last Name -
=UPPER(IF(ISNUMBER(FIND(",",A1)),TRIM(LEFT(A1,FIND (",",A1)-1)),TRIM(LEFT(A1,FIND("
",A1)-1))))


Thanks
Ruan

The formula will return #VALUE! if cell A1 is empty, or if it contains a
number. I understood it was to contain something like "Bond, James P.", and
you wanted only the "James" part extracted. It's a fairly long formula,
perhaps email word-wrap messed it up......it all goes in one cell.

Vaya con Dios,
Chuck, CABGx3



"Ruan" wrote:

I am getting "#VALUE!" error.


"CLR" wrote in message
...
=MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1)

Vaya con Dios,
Chuck, CABGx3



"Ruan" wrote:

Hello,

I was able to extract the Last Name from a string, however I am having
difficulties extracting the First Name, especially when there is a Middle
Initial involved.

Example: Bond, James P.

I just need to extract "James".

Here is my formula I used for extracting the Last Name -
=UPPER(IF(ISNUMBER(FIND(",",A1)),TRIM(LEFT(A1,FIND (",",A1)-1)),TRIM(LEFT(A1,FIND("
",A1)-1))))


Thanks
Ruan

Sorry, I should have been more specific. Not all the names have a Middle
Initial. So, when I don't have a middle initial (Bond, James), your formula
displays an error. Otherwise, it works perfectly for the names with a Middle
Initial.

Ruan


"CLR" wrote in message
...
The formula will return #VALUE! if cell A1 is empty, or if it contains a
number. I understood it was to contain something like "Bond, James P.",
and
you wanted only the "James" part extracted. It's a fairly long formula,
perhaps email word-wrap messed it up......it all goes in one cell.

Vaya con Dios,
Chuck, CABGx3



"Ruan" wrote:

I am getting "#VALUE!" error.


"CLR" wrote in message
...
=MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)-FIND("
",A1,1)-1)

Vaya con Dios,
Chuck, CABGx3



"Ruan" wrote:

Hello,

I was able to extract the Last Name from a string, however I am having
difficulties extracting the First Name, especially when there is a
Middle
Initial involved.

Example: Bond, James P.

I just need to extract "James".

Here is my formula I used for extracting the Last Name -
=UPPER(IF(ISNUMBER(FIND(",",A1)),TRIM(LEFT(A1,FIND (",",A1)-1)),TRIM(LEFT(A1,FIND("
",A1)-1))))


Thanks
Ruan

Try

=MID(A1,FIND(" ",A1)+1,FIND(" ",A1&" ",FIND(" ",A1)-FIND(" ",A1)+1)-1)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Ruan" wrote in message
...
Sorry, I should have been more specific. Not all the names have a Middle
Initial. So, when I don't have a middle initial (Bond, James), your
formula
displays an error. Otherwise, it works perfectly for the names with a
Middle
Initial.

Ruan


"CLR" wrote in message
...
The formula will return #VALUE! if cell A1 is empty, or if it contains a
number. I understood it was to contain something like "Bond, James P.",
and
you wanted only the "James" part extracted. It's a fairly long formula,
perhaps email word-wrap messed it up......it all goes in one cell.

Vaya con Dios,
Chuck, CABGx3



"Ruan" wrote:

I am getting "#VALUE!" error.


"CLR" wrote in message
...
=MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)-FIND("
",A1,1)-1)

Vaya con Dios,
Chuck, CABGx3



"Ruan" wrote:

Hello,

I was able to extract the Last Name from a string, however I am
having
difficulties extracting the First Name, especially when there is a
Middle
Initial involved.

Example: Bond, James P.

I just need to extract "James".

Here is my formula I used for extracting the Last Name -

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


Thanks
Ruan

Try this for the first word

=IF(ISERR(FIND(" ",A2)),A2,IF(RIGHT(LEFT(A2,FIND(" ",A2)-1))=",",LEFT(A2,FIND(" ",A2)-2),LEFT(A2,FIND(" ",A2)-1)))


Last word
=IF(ISERR(FIND(" ",A2)),A2,MID(A2,FIND("^^",SUBSTITUTE(A2," ","^^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,1024))


This Add-in make it easy to insert the formulas in a new column
http://www.rondebruin.nl/datarefiner.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ruan" wrote in message ...
Hello,

I was able to extract the Last Name from a string, however I am having difficulties extracting the First Name, especially when
there is a Middle Initial involved.

Example: Bond, James P.

I just need to extract "James".

Here is my formula I used for extracting the Last Name -
=UPPER(IF(ISNUMBER(FIND(",",A1)),TRIM(LEFT(A1,FIND (",",A1)-1)),TRIM(LEFT(A1,FIND(" ",A1)-1))))


Thanks
Ruan