What is JotForm?
JotForm is a free online form builder which helps you create online forms without writing a single line of code. No sign-up required.
At JotForm, we want to make sure that you’re getting the online form builder help that you need. Our friendly customer support team is available 24/7.
We believe that if one user has a question, there could be more users who may have the same question. This is why many of our support forum threads are public and available to be searched and viewed. If you’d like help immediately, feel free to search for a similar question, or submit your question or concern.
How can I separate multiple selections from a checklist once exported to Excel?Asked by kaeleyFCNL on August 09, 2016 at 02:25 PM
There's a checklist option on the form I am using. When the data is exported to Excel, all the checked options generate into one long line of text with no spaces or characters between them. How can I either a) separate out selected options with a special character or b) have the checked options show up in separate cells?
Sorry, but unfortunately there is no easy way to separate the selected values of a check box in different cells. You would need to do this manually with excel formulas.
If you like, you can create hidden fields for each selection. Using the FORM CALCULATION widget, you can capture the selected value and place it in a hidden field. Each hidden field will be placed in its own cell. This could be a workaround for you.
This is based on your specification that you've exported / downloaded your submission results in MS Excel format. Below are the answers.
Note: The Excel method I used is tedious compared to the solution if you're going to integrate with or use Google Sheets. If you prefer to use Google Sheets, please scroll down below for the easiest solution.
Disclaimer: I'm not an Excel expert - just sharing with you what I currently know.
How can I either a) separate out selected options with a special character or ...
You can achieve this by using the Substitute() function in MS Excel (fig. 1). Basically, we just reference the cell we want to manipulate and specify that CHAR(10) (new line) will be substituted with a comma. You will then see that the checked items that initially doesn't have spaces in between them will now be comma separated values (fig. 1).
... b) have the checked options show up in separate cells?
After doing the steps above, simply copy the entire contents of Column J > Right click on Column J > Paste Special > select Values > click OK (fig. 2).
Then select Column J again > click Data from the Ribbon > click Text to Columns > select Delimited > Next > check Comma > Next > under Destination, choose the next column to the right > click Finish (fig. 3 - animated gif).
- Randy, fellow JotForm user.
Google sheets solution - very easy :)
Simply use the SPLIT() function, then reference the cell you want to split, enter CHAR(10) to tell where to split then click enter (fig. 4).