Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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

share|improve this answer
    
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 '11 at 0:39

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.

share|improve this answer
1  
Or if you're using Notepad++ or any good text editor, you can use the escape sequence \t for tab. –  Christian Mann Dec 4 '11 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. –  Tony Dallimore Dec 4 '11 at 21:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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