You could use text to columns (space delimited), then in a separate column
concatenate your data back

B1 = Dr.
C1 = &
D1 = Mrs.
E1 = Smith

in cell A1 use =A1&" "&B1&" "&C1. Then copy cell A1 and click Edit/Paste
Special - values and the data will be hardcoded. then you can delete the
original data and cut/paste this new data where the old used to be.

I assume you have a number of occurences like this. After using the text to
columns with the comma delimiter, I would try to get everything that did not
split into a group. Let's say the data looks like

A B C
1 Smith Joe
2 Dr. & Mrs. Smith

If you want to remember what the original order is, number the data in
column A all the way down. Then sort columns A:C using column C, descending
order. Everything that didn't split s/b at the top. Then you could try
splitting the data again using space delimited, enter a formula to
concatenate back the first part of the name. Use Copy/Paste Special to
hardcode the data you concatenated then cut/paste your new first name over
the old data. Move things around/delete stuff you no longer need.

Then you can sort your table again using the numbering you set up in column
A to put things back.






Then you can copy

"Deena at DCH FD" wrote:

Thank you. Do you have any advice on separating out "Dr. & Mrs. Smith" into
2 columns. ie: cell a1=Dr. & Mrs. cell b1=Smith

I've been playing around with using a "." as the separator, but it splits
the cell into 3 columns...

"JMB" wrote:

click Data/Text To Columns
click Delimited
Check Comma (unchecking any other delimiters that may be checked)
click Finish

Backup before trying

"Deena at DCH FD" wrote:

I have an excel spreadsheet with first and last name in the same column.
ie:smith,jon. I need to have the last name in one column and the first name
in the next.

How can use .csv to break this into 2 separate columns: cell a1=smith, cell
b1=jon

(I don't know anything about using 'comma separated values' so any detail
info would be greatly apprecaited!