On Tue, 22 May 2007 20:13:37 -0700, "Bruce" wrote:

I get assignments with the name in various formats.
First & Spouse LName
LName, First AND Spouse
Lname, First
First LName , Spouse Lname
First LName
etc....

I am trying to figure out a way to get First (and spouse seperated with a "
& " ) in one cell and then the lastename in the next cell.
How could I do this?

Thanks
Bruce


One big problem in trying to do this is the

"etc..."

If you do not define your formats, you will need to program some kind of
Artificial Intelligence to determine that a format is unknown, and how to parse
it.

Also, in example 4, will the two LNames be the same?

Is LName always one word? If not, what are the possible variations?

It might be easier to coerce an intelligent data entry method, rather than
allowing a random method.

For the examples you post, it is relatively simple to define LName as the first
word which is followed by a comma, or a <space comma, or (if no commas) the
end of the line.

Using Longre's morefunc.xll add-in (free and downloadable from
http://xcell05.free.fr/

The regular expression formula:

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

will extract LName from the examples you give above.

But to do any more requires a complete definition of the various formats that
will have to be dealt with. As I wrote, much easier to coerce the proper
format -- perhaps using some kind of well defined input form.
--ron