Spilt Entire Name into Two columns

[Jeff Farrar]

--
Jeff Farrar
(e-mail address removed).(donotspam)
MCSE, NT 4.0, W2K

 

[Bob Phillips]

Data>Text To Columns

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jeff Farrar]

Sorry...I inadvertently hit the "Enter" key and submitted a blank post.

My mailing list spreadsheet of 1200 names has column A with the entire name
in the format of Doe, John. Our CEO wants a personalized letter sent to
everyone on the list which reads, Dear “First Nameâ€. I am able to split this
column into 2 columns and accomplish this BUT I have quite a few columns
where I am having problems. Some of the problem names are:

Smith, James F. {The middle initial is the problem-it’s placed into a
separate column}
Jones, Dr. Mike G. {the ‘Dr.†is the problem-resulting split is Dr.
Mike-should read “Dr. Jones.}

There are really not enough of these errors for it to be a large issue and
consume the board’s time and resources.

If there is a simple solution, though, I’d be interested in any ideas as I
can correct the “errors†manually.

Thank you,

Jeff Farrar

 

[Chip Pearson]

Jeff,

Parsing names is a very complicated task if you want to accurately parse a
variety of name styles. For example,

St Clair Dr John James Jr MD

Writing code to accurately parse out the first name, middle name (if any),
last name, honorific, and suffix can get extremely complicated. I have some
code at http://www.cpearson.com/excel/firstlast.htm that might help you get
started, but it is a difficult nut to crack. Unfortunately, there is no
simple way to do it.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Jeff Farrar]

Thanks Chip...your VB code worked wonders on our spreadsheet. We have saved
it as well as the link to your site where we obtained the code. Your hard
work in writing the code is very much appreciated as it saved us a ton of
work. And, in the IT Dept., it seems as though we are never lacking in some
task to perform.

Thanks Again,

Jeff Farrar