On Tue, 14 Feb 2006 15:08:50 -0600, SlipperyPete
wrote:


OK -- thanks for the firstname suggestion; no problem.

For extracting the lastname, I've tried and can't seem to get a
function to consistently work. It seems the length of the surname is
creating problems....suggestions?

eg.
Bar, Dr. Drinks
Help, Dr. Doctor
Banana, Dr. Fruity
Hippopotomous, Dr. Hairy

Using a function like this:
=RIGHT(A14,FIND("Dr. ",A14))

Returns results like this:
Drinks
Doctor
r. Fruity
omous, Dr. Hairy

Is there a function that will extract consistently everything from the
right of "Dr. "?

Show me the light -- I'm sick of playing around with this!!
Thanks!!!
Pete

There are a lot of ways to skin a cat. Something called regular expressions
are designed for this kind of text manipulation. They can be implemented by
downloading and installing Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then the following formulas can be used:

First Word (i.e. the Last Name)

=REGEX.MID(A1,"\w+")

Last Word (i.e. the first name)

=REGEX.MID(A1,"\w+",-1)

Word after "Dr. " (on your data set will return the same as Last Word):

=REGEX.MID(TRIM(A1),"(?<=Dr. )\w+")

All words after "Dr. ": (i.e. Banana, Dr. Fruity Hairy -- Fruity Hairy)

=REGEX.MID(TRIM(A1),"(?<=Dr. ).*")

and many other possibilities.




--ron