splitting text to multiple columns

[maryj]

I have a list of people's names in a cell. Some examples are:
Mr. John Doe
Mrs. Jane S. Smith
Ms. Cynthia Jones

I need to split their titles, first name with Middle initial and Last name
into 3 seperate columns. Using a post on this site, I was able to extract
the title, but am unsure how to pull the other information.

Thanks for you help!

 

[Don Guillett]

How about
data>text to columns>delimited>space

 

[maryj]

That would work except if the person has a middle initial listed, that needs
to stay with the first name, and not all people have a middle initial.

Thanks for the idea though.

 

[Don Guillett]

Chip has some extensive discussion.
http://www.cpearson.com

 

[David McRitchie]

In addition you might take a look at SepTerm and SepLastTerm
in http://www.mvps.org/dmcritchie/excel/join.htm
it's a matter of what your data actually is and I think this would
fit your situation. You will have to join names like de Bruin
first though like de_Bruin and the remove the underscore later.

Insert two new columns to the right of the name
first use the SepTerm macro
then use the SepLastTerm macro on the 2nd column.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[maryj]

Thanks Dave. The macros worked perfectly!

In addition you might take a look at SepTerm and SepLastTerm
in http://www.mvps.org/dmcritchie/excel/join.htm
it's a matter of what your data actually is and I think this would
fit your situation. You will have to join names like de Bruin
first though like de_Bruin and the remove the underscore later.

Insert two new columns to the right of the name
first use the SepTerm macro
then use the SepLastTerm macro on the 2nd column.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Chip has some extensive discussion.
http://www.cpearson.com