software

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 quot;Jamesquot;.

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

=MID(A1,FIND(quot; quot;,A1,1) 1,FIND(quot; quot;,A1,FIND(quot; quot;,A1,1) 1)-FIND(quot; quot;,A1,1)-1)

Vaya con Dios,
Chuck, CABGx3
quot;Ruanquot; wrote:

gt; Hello,
gt;
gt; I was able to extract the Last Name from a string, however I am having
gt; difficulties extracting the First Name, especially when there is a Middle
gt; Initial involved.
gt;
gt; Example: Bond, James P.
gt;
gt; I just need to extract quot;Jamesquot;.
gt;
gt; Here is my formula I used for extracting the Last Name -
gt; =UPPER(IF(ISNUMBER(FIND(quot;,quot;,A1)),TRIM(LEFT(A1,FIND (quot;,quot;,A1)-1)),TRIM(LEFT(A1,FIND(quot;
gt; quot;,A1)-1))))
gt;
gt;
gt; Thanks
gt; Ruan
gt;
gt;
gt;
gt;

I am getting quot;#VALUE!quot; error.quot;CLRquot; gt; wrote in message
...
gt; =MID(A1,FIND(quot; quot;,A1,1) 1,FIND(quot; quot;,A1,FIND(quot; quot;,A1,1) 1)-FIND(quot; quot;,A1,1)-1)
gt;
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;
gt;
gt; quot;Ruanquot; wrote:
gt;
gt;gt; Hello,
gt;gt;
gt;gt; I was able to extract the Last Name from a string, however I am having
gt;gt; difficulties extracting the First Name, especially when there is a Middle
gt;gt; Initial involved.
gt;gt;
gt;gt; Example: Bond, James P.
gt;gt;
gt;gt; I just need to extract quot;Jamesquot;.
gt;gt;
gt;gt; Here is my formula I used for extracting the Last Name -
gt;gt; =UPPER(IF(ISNUMBER(FIND(quot;,quot;,A1)),TRIM(LEFT(A1,FIND (quot;,quot;,A1)-1)),TRIM(LEFT(A1,FIND(quot;
gt;gt; quot;,A1)-1))))
gt;gt;
gt;gt;
gt;gt; Thanks
gt;gt; Ruan
gt;gt;
gt;gt;
gt;gt;
gt;gt;
Try this for the first word

=IF(ISERR(FIND(quot; quot;,A2)),A2,IF(RIGHT(LEFT(A2,FIND(quot; quot;,A2)-1))=quot;,quot;,LEFT(A2,FIND(quot; quot;,A2)-2),LEFT(A2,FIND(quot; quot;,A2)-1)))Last word
=IF(ISERR(FIND(quot; quot;,A2)),A2,MID(A2,FIND(quot;^^quot;,SUBSTITUTE(A2,quot; quot;,quot;^^quot;,LEN(A2)-LEN(SUBSTITUTE(A2,quot; quot;,quot;quot;)))) 1,1024))This Add-in make it easy to insert the formulas in a new column
www.rondebruin.nl/datarefiner.htm
--
Regards Ron de Bruin
www.rondebruin.nlquot;Ruanquot; gt; wrote in message ...
gt; Hello,
gt;
gt; I was able to extract the Last Name from a string, however I am having difficulties extracting the First Name, especially when
gt; there is a Middle Initial involved.
gt;
gt; Example: Bond, James P.
gt;
gt; I just need to extract quot;Jamesquot;.
gt;
gt; Here is my formula I used for extracting the Last Name -
gt; =UPPER(IF(ISNUMBER(FIND(quot;,quot;,A1)),TRIM(LEFT(A1,FIND (quot;,quot;,A1)-1)),TRIM(LEFT(A1,FIND(quot; quot;,A1)-1))))
gt;
gt;
gt; Thanks
gt; Ruan
gt;
gt;
gt;
The formula will return #VALUE! if cell A1 is empty, or if it contains a
number. I understood it was to contain something like quot;Bond, James P.quot;, and
you wanted only the quot;Jamesquot; 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
quot;Ruanquot; wrote:

gt; I am getting quot;#VALUE!quot; error.
gt;
gt;
gt; quot;CLRquot; gt; wrote in message
gt; ...
gt; gt; =MID(A1,FIND(quot; quot;,A1,1) 1,FIND(quot; quot;,A1,FIND(quot; quot;,A1,1) 1)-FIND(quot; quot;,A1,1)-1)
gt; gt;
gt; gt; Vaya con Dios,
gt; gt; Chuck, CABGx3
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Ruanquot; wrote:
gt; gt;
gt; gt;gt; Hello,
gt; gt;gt;
gt; gt;gt; I was able to extract the Last Name from a string, however I am having
gt; gt;gt; difficulties extracting the First Name, especially when there is a Middle
gt; gt;gt; Initial involved.
gt; gt;gt;
gt; gt;gt; Example: Bond, James P.
gt; gt;gt;
gt; gt;gt; I just need to extract quot;Jamesquot;.
gt; gt;gt;
gt; gt;gt; Here is my formula I used for extracting the Last Name -
gt; gt;gt; =UPPER(IF(ISNUMBER(FIND(quot;,quot;,A1)),TRIM(LEFT(A1,FIND (quot;,quot;,A1)-1)),TRIM(LEFT(A1,FIND(quot;
gt; gt;gt; quot;,A1)-1))))
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; Thanks
gt; gt;gt; Ruan
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

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.

Ruanquot;CLRquot; gt; wrote in message
...
gt; The formula will return #VALUE! if cell A1 is empty, or if it contains a
gt; number. I understood it was to contain something like quot;Bond, James P.quot;,
gt; and
gt; you wanted only the quot;Jamesquot; part extracted. It's a fairly long formula,
gt; perhaps email word-wrap messed it up......it all goes in one cell.
gt;
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;
gt;
gt; quot;Ruanquot; wrote:
gt;
gt;gt; I am getting quot;#VALUE!quot; error.
gt;gt;
gt;gt;
gt;gt; quot;CLRquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; =MID(A1,FIND(quot; quot;,A1,1) 1,FIND(quot; quot;,A1,FIND(quot; quot;,A1,1) 1)-FIND(quot;
gt;gt; gt; quot;,A1,1)-1)
gt;gt; gt;
gt;gt; gt; Vaya con Dios,
gt;gt; gt; Chuck, CABGx3
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; quot;Ruanquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; Hello,
gt;gt; gt;gt;
gt;gt; gt;gt; I was able to extract the Last Name from a string, however I am having
gt;gt; gt;gt; difficulties extracting the First Name, especially when there is a
gt;gt; gt;gt; Middle
gt;gt; gt;gt; Initial involved.
gt;gt; gt;gt;
gt;gt; gt;gt; Example: Bond, James P.
gt;gt; gt;gt;
gt;gt; gt;gt; I just need to extract quot;Jamesquot;.
gt;gt; gt;gt;
gt;gt; gt;gt; Here is my formula I used for extracting the Last Name -
gt;gt; gt;gt; =UPPER(IF(ISNUMBER(FIND(quot;,quot;,A1)),TRIM(LEFT(A1,FIND (quot;,quot;,A1)-1)),TRIM(LEFT(A1,FIND(quot;
gt;gt; gt;gt; quot;,A1)-1))))
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; Thanks
gt;gt; gt;gt; Ruan
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;
Try

=MID(A1,FIND(quot; quot;,A1) 1,FIND(quot; quot;,A1amp;quot; quot;,FIND(quot; quot;,A1)-FIND(quot; quot;,A1) 1)-1)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Ruanquot; gt; wrote in message
...
gt; Sorry, I should have been more specific. Not all the names have a Middle
gt; Initial. So, when I don't have a middle initial (Bond, James), your
formula
gt; displays an error. Otherwise, it works perfectly for the names with a
Middle
gt; Initial.
gt;
gt; Ruan
gt;
gt;
gt; quot;CLRquot; gt; wrote in message
gt; ...
gt; gt; The formula will return #VALUE! if cell A1 is empty, or if it contains a
gt; gt; number. I understood it was to contain something like quot;Bond, James P.quot;,
gt; gt; and
gt; gt; you wanted only the quot;Jamesquot; part extracted. It's a fairly long formula,
gt; gt; perhaps email word-wrap messed it up......it all goes in one cell.
gt; gt;
gt; gt; Vaya con Dios,
gt; gt; Chuck, CABGx3
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Ruanquot; wrote:
gt; gt;
gt; gt;gt; I am getting quot;#VALUE!quot; error.
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;CLRquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; =MID(A1,FIND(quot; quot;,A1,1) 1,FIND(quot; quot;,A1,FIND(quot; quot;,A1,1) 1)-FIND(quot;
gt; gt;gt; gt; quot;,A1,1)-1)
gt; gt;gt; gt;
gt; gt;gt; gt; Vaya con Dios,
gt; gt;gt; gt; Chuck, CABGx3
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt; gt; quot;Ruanquot; wrote:
gt; gt;gt; gt;
gt; gt;gt; gt;gt; Hello,
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; I was able to extract the Last Name from a string, however I am
having
gt; gt;gt; gt;gt; difficulties extracting the First Name, especially when there is a
gt; gt;gt; gt;gt; Middle
gt; gt;gt; gt;gt; Initial involved.
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Example: Bond, James P.
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; I just need to extract quot;Jamesquot;.
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Here is my formula I used for extracting the Last Name -
gt; gt;gt; gt;gt;
=UPPER(IF(ISNUMBER(FIND(quot;,quot;,A1)),TRIM(LEFT(A1,FIND (quot;,quot;,A1)-1)),TRIM(LEFT(A1,
FIND(quot;
gt; gt;gt; gt;gt; quot;,A1)-1))))
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Thanks
gt; gt;gt; gt;gt; Ruan
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
Perfect. Thank you so much Bob.quot;Bob Phillipsquot; gt; wrote in message
...
gt; Try
gt;
gt; =MID(A1,FIND(quot; quot;,A1) 1,FIND(quot; quot;,A1amp;quot; quot;,FIND(quot; quot;,A1)-FIND(quot; quot;,A1) 1)-1)
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;Ruanquot; gt; wrote in message
gt; ...
gt;gt; Sorry, I should have been more specific. Not all the names have a Middle
gt;gt; Initial. So, when I don't have a middle initial (Bond, James), your
gt; formula
gt;gt; displays an error. Otherwise, it works perfectly for the names with a
gt; Middle
gt;gt; Initial.
gt;gt;
gt;gt; Ruan
gt;gt;
gt;gt;
gt;gt; quot;CLRquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; The formula will return #VALUE! if cell A1 is empty, or if it contains
gt;gt; gt; a
gt;gt; gt; number. I understood it was to contain something like quot;Bond, James
gt;gt; gt; P.quot;,
gt;gt; gt; and
gt;gt; gt; you wanted only the quot;Jamesquot; part extracted. It's a fairly long
gt;gt; gt; formula,
gt;gt; gt; perhaps email word-wrap messed it up......it all goes in one cell.
gt;gt; gt;
gt;gt; gt; Vaya con Dios,
gt;gt; gt; Chuck, CABGx3
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; quot;Ruanquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; I am getting quot;#VALUE!quot; error.
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; quot;CLRquot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt; =MID(A1,FIND(quot; quot;,A1,1) 1,FIND(quot; quot;,A1,FIND(quot; quot;,A1,1) 1)-FIND(quot;
gt;gt; gt;gt; gt; quot;,A1,1)-1)
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Vaya con Dios,
gt;gt; gt;gt; gt; Chuck, CABGx3
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; quot;Ruanquot; wrote:
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt;gt; Hello,
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; I was able to extract the Last Name from a string, however I am
gt; having
gt;gt; gt;gt; gt;gt; difficulties extracting the First Name, especially when there is a
gt;gt; gt;gt; gt;gt; Middle
gt;gt; gt;gt; gt;gt; Initial involved.
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; Example: Bond, James P.
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; I just need to extract quot;Jamesquot;.
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; Here is my formula I used for extracting the Last Name -
gt;gt; gt;gt; gt;gt;
gt; =UPPER(IF(ISNUMBER(FIND(quot;,quot;,A1)),TRIM(LEFT(A1,FIND (quot;,quot;,A1)-1)),TRIM(LEFT(A1,
gt; FIND(quot;
gt;gt; gt;gt; gt;gt; quot;,A1)-1))))
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; Thanks
gt;gt; gt;gt; gt;gt; Ruan
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;
gt;
Hi Bob.........

Your formula overcame the OP's objections to my original one, but will
return strange results with different combinations of longer and shorter
names, and with cells containing lastname, firstname middlename
For example:
Washington, Bill returns Bill (as it should)
Washington, Bill P. returns Bill P.
Washington, Bill Percival returns Bill Perciv
Bond, Benjamin returns Benja

This one appears to work better.....
=IF(A1=quot;quot;,quot;quot;,IF(COUNTIF(A1,quot;*,*quot;)gt;0,MID(A1,FIND(quot; quot;,A1,1) 1,FIND(quot; quot;,A1amp;quot;
quot;,FIND(quot; quot;,A1,1) 1)-FIND(quot; quot;,A1,1)-1),A1))Vaya con Dios,
Chuck, CABGx3
quot;Bob Phillipsquot; wrote:

gt; Try
gt;
gt; =MID(A1,FIND(quot; quot;,A1) 1,FIND(quot; quot;,A1amp;quot; quot;,FIND(quot; quot;,A1)-FIND(quot; quot;,A1) 1)-1)
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;Ruanquot; gt; wrote in message
gt; ...
gt; gt; Sorry, I should have been more specific. Not all the names have a Middle
gt; gt; Initial. So, when I don't have a middle initial (Bond, James), your
gt; formula
gt; gt; displays an error. Otherwise, it works perfectly for the names with a
gt; Middle
gt; gt; Initial.
gt; gt;
gt; gt; Ruan
gt; gt;
gt; gt;
gt; gt; quot;CLRquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; The formula will return #VALUE! if cell A1 is empty, or if it contains a
gt; gt; gt; number. I understood it was to contain something like quot;Bond, James P.quot;,
gt; gt; gt; and
gt; gt; gt; you wanted only the quot;Jamesquot; part extracted. It's a fairly long formula,
gt; gt; gt; perhaps email word-wrap messed it up......it all goes in one cell.
gt; gt; gt;
gt; gt; gt; Vaya con Dios,
gt; gt; gt; Chuck, CABGx3
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Ruanquot; wrote:
gt; gt; gt;
gt; gt; gt;gt; I am getting quot;#VALUE!quot; error.
gt; gt; gt;gt;
gt; gt; gt;gt;
gt; gt; gt;gt; quot;CLRquot; gt; wrote in message
gt; gt; gt;gt; ...
gt; gt; gt;gt; gt; =MID(A1,FIND(quot; quot;,A1,1) 1,FIND(quot; quot;,A1,FIND(quot; quot;,A1,1) 1)-FIND(quot;
gt; gt; gt;gt; gt; quot;,A1,1)-1)
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; Vaya con Dios,
gt; gt; gt;gt; gt; Chuck, CABGx3
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; quot;Ruanquot; wrote:
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt;gt; Hello,
gt; gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt; I was able to extract the Last Name from a string, however I am
gt; having
gt; gt; gt;gt; gt;gt; difficulties extracting the First Name, especially when there is a
gt; gt; gt;gt; gt;gt; Middle
gt; gt; gt;gt; gt;gt; Initial involved.
gt; gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt; Example: Bond, James P.
gt; gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt; I just need to extract quot;Jamesquot;.
gt; gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt; Here is my formula I used for extracting the Last Name -
gt; gt; gt;gt; gt;gt;
gt; =UPPER(IF(ISNUMBER(FIND(quot;,quot;,A1)),TRIM(LEFT(A1,FIND (quot;,quot;,A1)-1)),TRIM(LEFT(A1,
gt; FIND(quot;
gt; gt; gt;gt; gt;gt; quot;,A1)-1))))
gt; gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt; Thanks
gt; gt; gt;gt; gt;gt; Ruan
gt; gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt;
gt; gt; gt;gt;
gt; gt; gt;gt;
gt; gt; gt;gt;
gt; gt;
gt; gt;
gt;
gt;
gt;

Hi Chuck,

As you know, extracting names can be fraught with problems lt;vbggt;. For
instance, just try your new solution with St. John, Ian

I just supplied a solution to the problem as defined. Personally, I have an
addin that uses regular expressions to split names, but again this works for
names like Bob Phillips, Bill Percival, Ian St John, Baron von Richtofen,
etc, but was not designed for Phillips, Bob, etc.

Nightmare isn't it lt;vbggt;

Bob

quot;CLRquot; gt; wrote in message
...
gt; Hi Bob.........
gt;
gt; Your formula overcame the OP's objections to my original one, but will
gt; return strange results with different combinations of longer and shorter
gt; names, and with cells containing lastname, firstname middlename
gt; For example:
gt; Washington, Bill returns Bill (as it should)
gt; Washington, Bill P. returns Bill P.
gt; Washington, Bill Percival returns Bill Perciv
gt; Bond, Benjamin returns Benja
gt;
gt; This one appears to work better.....
gt; =IF(A1=quot;quot;,quot;quot;,IF(COUNTIF(A1,quot;*,*quot;)gt;0,MID(A1,FIND(quot; quot;,A1,1) 1,FIND(quot; quot;,A1amp;quot;
gt; quot;,FIND(quot; quot;,A1,1) 1)-FIND(quot; quot;,A1,1)-1),A1))
gt;
gt;
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Try
gt; gt;
gt; gt; =MID(A1,FIND(quot; quot;,A1) 1,FIND(quot; quot;,A1amp;quot; quot;,FIND(quot; quot;,A1)-FIND(quot; quot;,A1) 1)-1)
gt; gt;
gt; gt; --
gt; gt;
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from the email address if mailing direct)
gt; gt;
gt; gt; quot;Ruanquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Sorry, I should have been more specific. Not all the names have a
Middle
gt; gt; gt; Initial. So, when I don't have a middle initial (Bond, James), your
gt; gt; formula
gt; gt; gt; displays an error. Otherwise, it works perfectly for the names with a
gt; gt; Middle
gt; gt; gt; Initial.
gt; gt; gt;
gt; gt; gt; Ruan
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;CLRquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; The formula will return #VALUE! if cell A1 is empty, or if it
contains a
gt; gt; gt; gt; number. I understood it was to contain something like quot;Bond, James
P.quot;,
gt; gt; gt; gt; and
gt; gt; gt; gt; you wanted only the quot;Jamesquot; part extracted. It's a fairly long
formula,
gt; gt; gt; gt; perhaps email word-wrap messed it up......it all goes in one cell.
gt; gt; gt; gt;
gt; gt; gt; gt; Vaya con Dios,
gt; gt; gt; gt; Chuck, CABGx3
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Ruanquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt;gt; I am getting quot;#VALUE!quot; error.
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt; quot;CLRquot; gt; wrote in message
gt; gt; gt; gt;gt; ...
gt; gt; gt; gt;gt; gt; =MID(A1,FIND(quot; quot;,A1,1) 1,FIND(quot; quot;,A1,FIND(quot; quot;,A1,1) 1)-FIND(quot;
gt; gt; gt; gt;gt; gt; quot;,A1,1)-1)
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;gt; gt; Vaya con Dios,
gt; gt; gt; gt;gt; gt; Chuck, CABGx3
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;gt; gt; quot;Ruanquot; wrote:
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;gt; gt;gt; Hello,
gt; gt; gt; gt;gt; gt;gt;
gt; gt; gt; gt;gt; gt;gt; I was able to extract the Last Name from a string, however I am
gt; gt; having
gt; gt; gt; gt;gt; gt;gt; difficulties extracting the First Name, especially when there is
a
gt; gt; gt; gt;gt; gt;gt; Middle
gt; gt; gt; gt;gt; gt;gt; Initial involved.
gt; gt; gt; gt;gt; gt;gt;
gt; gt; gt; gt;gt; gt;gt; Example: Bond, James P.
gt; gt; gt; gt;gt; gt;gt;
gt; gt; gt; gt;gt; gt;gt; I just need to extract quot;Jamesquot;.
gt; gt; gt; gt;gt; gt;gt;
gt; gt; gt; gt;gt; gt;gt; Here is my formula I used for extracting the Last Name -
gt; gt; gt; gt;gt; gt;gt;
gt; gt;
=UPPER(IF(ISNUMBER(FIND(quot;,quot;,A1)),TRIM(LEFT(A1,FIND (quot;,quot;,A1)-1)),TRIM(LEFT(A1,
gt; gt; FIND(quot;
gt; gt; gt; gt;gt; gt;gt; quot;,A1)-1))))
gt; gt; gt; gt;gt; gt;gt;
gt; gt; gt; gt;gt; gt;gt;
gt; gt; gt; gt;gt; gt;gt; Thanks
gt; gt; gt; gt;gt; gt;gt; Ruan
gt; gt; gt; gt;gt; gt;gt;
gt; gt; gt; gt;gt; gt;gt;
gt; gt; gt; gt;gt; gt;gt;
gt; gt; gt; gt;gt; gt;gt;
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt;
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;