Re: A Challenge


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 then
> 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 macro
> 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 you
might find useful

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

In this thread
http://www.excelforum.com/showthread.php?t=391763&highlight=splitting+names
Bob Phillips in the 9th post says "I have a RegExp solution for any
combination. It gets worse, because you can
have name like William A. Carson Jr., or Ian St. John, or even Marquis
de
Sade." so I am sure he can help or provide more detail about his RegExp
solution


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783
View this thread: http://www.excelforum.com/showthread.php?threadid=399787

.