On Thu, 8 Dec 2005 12:30:02 -0800, Bert_Lady
wrote:

I need to split up names in one field to multiple fields. I am using the
extractelement function. My problem: some fields have a title and some don't.
Therefore, I end up with the Title in the First name field and the first name
in the Last name field.

How do I write this to indicate tht if there is NO title to skip and move to
the First name field? OR when there is no title, but a first name, middle
initial and last name? I don't have consistancy in the naming fo the field I
am extracting from.

I might have: Bert T. Smith, Bert Smith, Mr. Bert Smith

Thanks!

1. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

2. Use the following formulas:

Title:

I assumed that a "Title" starts at the beginning of the entry, and is
terminated by a dot followed by a space.

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

First Name:

First Name is the first word that is followed by a space (no dot).

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

Last Name:

Last Name is the last word in the string:

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

===============================
Possible problems include names like:

Mr. T. Bert Smith, Jr.

This can be handled if necessary, but if it is going to be an issue, we would
need more precise specifications.


--ron