mjones Wrote:
Hi,

My client is expecting me to perform miracles. I thought I'd throw
this one out for those who like a challenge. Many large spreadsheets
(so large they have to be split up to get them into Excel) have a name
column. An example of the type of data might be:

Mary Jane Fox
Marg F. Smith
Matt Del Sandro
Frank George Di Marco
Paul Joseph Rental/Storage Ltd.
Kim O'Neil
Don Jones-St. James
Andrew K.G. Black
Mr. and Mrs. Albert F. Casey
Brown
Sisters of the Holly Cross of 3rd Street
M Thomas
Christine J. Main & Ken Henry

My client wants me to separate the last name into its own column so
they can run duplicate tests with other files against it and other
things. Remember, there could be 65,000 names.

As you can imagine, this is very time consuming so anything will help.
I have been using space delimited to separate the fields and the
doing
things like sorting or splitting off the first two characters and
sorting what's left to separate out the single initials and then
concatenating back again.

I know this can't be an exact science, but I'm wondering if a macr
can
be written to perform some of the work and perhaps stop and ask about
questionable names. For example,

- single letters with or without periods would be first names
- a database of words could show companies and take the whole word as
last name like Ltd. or Co.
- a database of words like Di, St., Del, O', or Le could be defined as
part of the last name
- one word names are last names
- take the last of two names and forget the first one
- stuff like that

When you use text to columns space delimited it puts the first word in
the first column and second in second column, etc. so depending upon
how many words are in the name, you never know which column the last
word will end up in. It would be nice to make it start in the last
column. We could use Access, too, if that would make any difference.

I don't expect to receive much help on this, but you never know. I
find some people in this newsgroup have the most amazing ideas.

Thanks,

Michele

Hi Michelle

Try a search for splitting names, this returns a number of threads yo
might find useful

I asked a similar, not so complex question not long ago and got som
excellent help, so try changing the title of your query to somethin
like "Help with splitting names"

In this threa
http://www.excelforum.com/showthread...splitting+name
Bob Phillips in the 9th post says "I have a RegExp solution for an
combination. It gets worse, because you can
have name like William A. Carson Jr., or Ian St. John, or even Marqui
de
Sade." so I am sure he can help or provide more detail about his RegEx
solutio

--
Paul Sheppar

-----------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...fo&userid=2478
View this thread: http://www.excelforum.com/showthread.php?threadid=39978