how do i split names in to two columns

[Guest]

I have a data column that has names listed like this; Ann & Bob Smith, Ann
Smith & Bob Smith. I know I can use the data-text to columns-delimiter for
names such as Ann Smith, but with space in between the examples above I am
getting rows of useless data and deleting the column won't work b/c the &
sign ends up in a column with last names in other rows all the way down(ex
below). I have limited exprience with excel and probably won't be able to
figure out how to write any code for this but if you explain it I will try it.

Column 1 Column 2 Column 3 Col 4 Col 5

Ann Smith
Ann & Bob Smith
Ann Smith & Bob
Smith

 

[Earl Kiosterud]

Jesse,

Use Text-To_Columns, specifying the &, and deselecting the other delimiters. Then do the
same with the separate columns using the space.

--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[Guest]

I think you will need a macro: are Ann & Bob Smith to appear in two rows
(and two columns) ?:

Ann Smith
Bob Smith

If you send me a sample file - all possible permutations please! - , I'll
have a go at a macro.

toppers <at> johntopley.fsnet.co.uk