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?

Solution 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

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

Comments

[Christian Mann]

Or if you're using Notepad++ or any good text editor, you can use the escape sequence \t for tab.

[mxke42]

I am not being allowed to add a comment. If the user has typed "Cat,,Dog" or ",Cat" replacing commas by tabs will leave a blank cell. If blank cells are to be removed, Replace all TAB Tab by TAB several times. This will remove all blank cells except those in column A.

[nhinkle]

This works well, but the possible problem is that if there's anything already in cells C or D, they'll get overwritten.