using CSV to break up text into multiple columns

[Guest]

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!

 

[Guest]

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

Backup before trying

 

[Guest]

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...

 

[brian]

As you need to have a comma after an item to separate it into the next
column, so first replace "Mrs. " (plus the space after the s dot) with
"Mrs.," and once that is done do what you were doing before. Hope that
helps.

Brian

 

[Guest]

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

 

[Guest]

One other thing, when you split the data using the space, just select the
data you need. Don't select the data that is already properly split. Text
to columns overwrites data that is in the columns to the right - so make sure
these are empty and be sure your data is backed up.