Separate First Name MI and Last Name into three Cells

I have a list of names in column A that contain the First Name, then a space,
the Middle Initial then a space and the last name. Some cells in column A
only contain a First name and a Last Name such as:

John A Doe
Jane Smith
Bill Jones
Sue Z Que

I want to separate the text into three columns; B, C & D. Column B = First
Name, Column C = MI and Column D = Last Name. I know how to use the MID,
LEFT and RIGHT functions, and I know there must be some way of finding the
spaces in a cell and separating the text from the spaces. Thanks for your
help.

Take a look at DataText To Column. That will separate the names but the
names with no Middle Name or initial will be split into 2 coumns
HTH
Sandy

"DBavirsha" wrote:

I have a list of names in column A that contain the First Name, then a space,
the Middle Initial then a space and the last name. Some cells in column A
only contain a First name and a Last Name such as:

John A Doe
Jane Smith
Bill Jones
Sue Z Que

I want to separate the text into three columns; B, C & D. Column B = First
Name, Column C = MI and Column D = Last Name. I know how to use the MID,
LEFT and RIGHT functions, and I know there must be some way of finding the
spaces in a cell and separating the text from the spaces. Thanks for your
help.

Check out Chip Pearson's site for help on this.

http://www.cpearson.com/excel/FirstLast.htm


Gord Dibben MS Excel MVP

On Thu, 6 Jul 2006 06:35:01 -0700, DBavirsha
wrote:

I have a list of names in column A that contain the First Name, then a space,
the Middle Initial then a space and the last name. Some cells in column A
only contain a First name and a Last Name such as:

John A Doe
Jane Smith
Bill Jones
Sue Z Que

I want to separate the text into three columns; B, C & D. Column B = First
Name, Column C = MI and Column D = Last Name. I know how to use the MID,
LEFT and RIGHT functions, and I know there must be some way of finding the
spaces in a cell and separating the text from the spaces. Thanks for your
help.

Thanks so much for the assistance. I got the information I needed from your
web site. Thanks also for the link to the website. It is loaded with Excel
help and I will refer to it often.

Dave

"Gord Dibben" wrote:

Check out Chip Pearson's site for help on this.

http://www.cpearson.com/excel/FirstLast.htm


Gord Dibben MS Excel MVP

On Thu, 6 Jul 2006 06:35:01 -0700, DBavirsha
wrote:

I have a list of names in column A that contain the First Name, then a space,
the Middle Initial then a space and the last name. Some cells in column A
only contain a First name and a Last Name such as:

John A Doe
Jane Smith
Bill Jones
Sue Z Que

I want to separate the text into three columns; B, C & D. Column B = First
Name, Column C = MI and Column D = Last Name. I know how to use the MID,
LEFT and RIGHT functions, and I know there must be some way of finding the
spaces in a cell and separating the text from the spaces. Thanks for your
help.