http://www.cpearson.com/excel/FirstLast.htm
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Craig" wrote in message
...
Hi,

I have many full names separated by spaces in many rows of column A. Some
full names have middle names or middle initials while some have a comma at
the end of their name, followed by a job title...if there is a comma at
the
end of their name, then it is followed by a job title.

I want the last name of each person separated out of col A and displayed
in
col B of the same row, the first name separated out of col A and displayed
in
col C of the same row, the middle initial or middle name separated out of
col
A and displayed in col D of the same row OR col D would be blank if the
name
in col A does not have a middle initial or name, and the job title
separated
out of col A and displayed in Col E of the same row OR col E would be
blank
if the name in col A does not have a job title.

For example, I need a function that can separate out full names in column
A
cells such as:

"John Smith" in cell A1 into 2 separate cells with "Smith" in B1 and
"John"
in C1 with nothing in cell D1 as there is no Middle name or middle initial
and nothing in cell E1 as there is no job title

"John Q. Smith" in cell A2 into 3 separate cells with "Smith" in B2 "John"
in C2 and "Q." in D2 with nothing in E2 as there is no job title

"John Quincy Smith" in cell A3 into 3 separate cells with "Smith" in B3
"John" in C3 and "Quincy" in D3 with nothing in E3 as there is no job
title

"John Q. Smith, Exec. Dir." in cell A4 into 4 separate cells with "Smith"
in
B4 "John" in C4 "Q." in D4 and "Exec. Dir." in E4

"John Q. Smith, Executive Director" in cell A5 into 4 separate cells with
"Smith" in B5 "John" in C5 "Q." in D5 and "Executive Director" in E5

"John Smith, Executive" in cell A6 into 3 separate cells with "Smith" in
B6
"John" in C6 with nothing in D6 as there is no middle name or middle
initial
and "Executive" in E6

Thank you very much for any assistance!

Craig