Splitting Up Text in One Cell To Many
 

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!

--
Bert_Lady

Splitting Up Text in One Cell To Many
 

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

Splitting Up Text in One Cell To Many
 

Thanks so much Ron. I did not do a very good job of expalining.

Just a bit more.

The title can include, not only Mr. Mrs., but also Judge, Justice, etc. So
not only titles ending with a period.
--
Bert_Lady


"Ron Rosenfeld" wrote:

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

Splitting Up Text in One Cell To Many
 

On Thu, 8 Dec 2005 15:50:02 -0800, Bert_Lady
wrote:

Thanks so much Ron. I did not do a very good job of expalining.

Just a bit more.

The title can include, not only Mr. Mrs., but also Judge, Justice, etc. So
not only titles ending with a period.
--

You will need to incorporate a list of all possible titles in the first
function.

For example:

=REGEX.MID(A1,"(Mr.)|(Mrs.)|(Ms.)|(Dr.)|(Judge)|(J ustice)")

Note that the titles are enclosed in parentheses and separated by pipes. You
can add to the list as you wish.

The following is based on the full name being in A1, and the Title (if any)
being in B1:

For the First Name, we also make a modification in the formula so that instead
of looking at the original string in A1, we process that string to remove the
Title, and then take the first word after that.

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

The Last Name formula remains unchanged:

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


--ron