How to break a spreadsheet containing CSV into multiple columns?

Question

I'm working on preparing data for export, and the client has put all of the data into a spreadsheet. Each field of data should be its own column. The problem is that the client erroneously put certain values into a single cell and separated them by commas instead of using separate cells.

So, is there a way to make excel go through all of the cells in a single column and break up the values into multiple columns, appending cells to the end of the row where necessary?

For example, cell D3:

[Data][...][Cat, Dog, Cow]

Should become cells D3, D4, and D5:

[Data][...][Cat][Dog][Cow]

Is there a macro or some other script that can be written?

Answer 1

I would use the text to columns feature of excel to break them out. Just ensure that you have empty columns to the right of your data.

ie

now highlight column B and select text to columns

Choose delimited on screen one

then

after you hit finish, your data should now look like

Answer 2

The simplest solution may be to copy the entire spread sheet into Notepad, where the cells are separated by TAB characters, and do a find and replace to turn the commas into tabs, then copy-paste back into Excel. Note that to actually input a tab in Notepad's find and replace dialogue, you will need to paste it in.