You are here: Home / Documentation / How To's / How to split out comma-separated values in survey results

How to split out comma-separated values in survey results

by nguyen — published Sep 04, 2008 11:56 AM, last modified Aug 16, 2016 10:50 AM
splitting values such as "choice1, choice2, choice3" into individual columns in survey results

If you have a survey that has a multi-select answer, when you export the survey results and open them with Excel you will see values such as in column J below:

J
K L
 choice1, choice2, choice3 otherquestionanswer
yetanotherquestionanswer
     

You may want to split out the column J values so that each choice is in a column of its own, so you end up like this:

J K L M
N
 choice1 choice2
choice3
otherquestionanswer yetanotherquestionanswer
         

To do that, you have to make room for the new columns that will get created.  Click on the header for the column to the right, in this case column J.  In the Insert menu, choose Columns.  Do that three times so you have 3 new columns in there (you only need as many new columns as there are possible choices for that question).  It'll look like this:

J K L M
N
 choice1, choice2, choice3

otherquestionanswer yetanotherquestionanswer
         

Click on column J header to select the entire column J.  Then in the Data menu, choose Text to Columns.  Check the box for "delimited", then the Next button, then check the box for Comma (uncheck the box for Tab), click Next, click Finished, and when it asks 'do you want to replace the contents of the destination cells' click OK.  That will replace column J with as many columns as needed to store each split-out value in its own column.

Navigation