Extracting First Name

Hi all

My formula is working for Phillips, Bob also but have other problems g


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


"Bob Phillips" wrote in message ...
Hi Chuck,

As you know, extracting names can be fraught with problems vbg. 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 vbg

Bob

"CLR" wrote in message
...
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="","",IF(COUNTIF(A1,"*,*")0,MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1&"
",FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1),A1))


Vaya con Dios,
Chuck, CABGx3



"Bob Phillips" wrote:

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

Hi Bob.........

Yeah, I hear 'ya.... LOL.......they seem to be a moving target...........

Vaya con Dios,
Chuck, CABGx3



"Bob Phillips" wrote:

Hi Chuck,

As you know, extracting names can be fraught with problems vbg. 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 vbg

Bob

"CLR" wrote in message
...
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="","",IF(COUNTIF(A1,"*,*")0,MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1&"
",FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1),A1))


Vaya con Dios,
Chuck, CABGx3



"Bob Phillips" wrote:

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

Such as with de Bruin, Ron vbg

Bob

"Ron de Bruin" wrote in message
...
Hi all

My formula is working for Phillips, Bob also but have other problems g


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


"Bob Phillips" wrote in message
...
Hi Chuck,

As you know, extracting names can be fraught with problems vbg. 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 vbg

Bob

"CLR" wrote in message
...
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="","",IF(COUNTIF(A1,"*,*")0,MID(A1,FIND(" ",A1,1)+1,FIND("
",A1&"
",FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1),A1))


Vaya con Dios,
Chuck, CABGx3



"Bob Phillips" wrote:

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