- kaeleyFCNLAsked 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?
- victorAnswered on August 09, 2016 at 04:03 PM
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.
- grade4pagasaAnswered on August 10, 2016 at 05:27 AM
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).