Seperating names into 2 columns

[dak11]

I have an excel sheet which has peoples names which I need to separate into 2 columns. With just 2 names is simple in that I can use the text to columns feature. The problem I'm having is some of the data is 2 people (husband and wife) where an & is used.

Example

John Doe
Jan Doe
John & Jane Doe
Bob Day
Bob & Sarah Day

Is there an easy way for me to put the last names (last word in each cell) into a column and the from part into a different one so that single people and partners are all listed in one column?

 

[vds1]

Hi Dak11

First you can use

Text-to-column | Delimited | select Other and inside the box type & | Next | Finish

After this process you will have to repeat the process with space as delimiter..

 

[dak11]

Thanks vds.

This doesn't seem to work like I need it to as it's separating all the names into their own columns.

Based on the above example.

I need the below in one column

John
Jan
John & Jane
Bob
Bob & Sarah

And the last names in another

 

[Rick Rothstein]

I have an excel sheet which has peoples names which I need to separate into 2 columns. With just 2 names is simple in that I can use the text to columns feature. The problem I'm having is some of the data is 2 people (husband and wife) where an & is used.

Example

John Doe
Jan Doe
John & Jane Doe
Bob Day
Bob & Sarah Day

Is there an easy way for me to put the last names (last word in each cell) into a column and the from part into a different one so that single people and partners are all listed in one column?

Select the column and then use Excel's Replace dialog box to replace space/ampersand/space with ampersand, then use Text To Columns to split the data using a space as the delimiter, then select the first names column and replace the ampersand with space/ampersand/space.

 

[vds1]

In C1,

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

In B1,

=TRIM(SUBSTITUTE(A1,C1,""))

Where A1 is your data,

Note that C1 is dependent on B1, so you should have column C drag down first and then column B

 

[dak11]

Thanks Rick.

Simple fix to a problem I was totally over thinking.