4

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?

2 Answers 2

4

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

initial state

now highlight column B and select text to columns

Choose delimited on screen one

then

text to columns setup

after you hit finish, your data should now look like

after

1
  • This works well, but the possible problem is that if there's anything already in cells C or D, they'll get overwritten.
    – nhinkle
    Dec 5, 2011 at 0:39
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.

2
  • 1
    Or if you're using Notepad++ or any good text editor, you can use the escape sequence \t for tab. Dec 4, 2011 at 20:33
  • 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. Dec 4, 2011 at 21:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.