On Thu, 5 Jan 2006 16:51:30 -0600, jermsalerms
wrote:


I want to take a clients 1st and last name that populates in cell 1 and
break it apart so that cell 2 only shows the last name and cell 3 shows
only the first name.

How do I write a formula to fill in the 1st word vs the 2nd word in
cell 1...also some have a middle initial that I do not want to be
included in either but some do not so I need it to recognize the
difference.

You could use regular expressions to handle all sorts of variations.

First, download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then for example, given:

John Doe
Ms John Doe
John J Doe
Mr. John Doe, Jr
Dr John J Doe, Jr
John Doe MD
John Doe Ph.D.



First Name:

=REGEX.MID(A1,"(?!D|M(\w{0,2}))(?<=\s|^)\w+\s")

Last Name including the Title

=REGEX.MID(A5,"\w+((,?\s)(Jr|Sr|II|III|IV|M\.?D|(? i)PH\.?D)(\.?))?$")

=================================

Rules:

Any first word will be omitted that either ends with a dot (.) or, if it starts
with an M or a D, and is followed by 0 to 2 letters (eg. Mrs. Ms Ms. M M. Dr
Dr. should all be excluded).

This, purposely will also exclude leading initials:

J. John Doe will also -- John for a first name. (This could be changed).

Various titles are delineated specifically, as I could not think of a general
rule that would include them all.


--ron