In my Survey123 survey, I have some questions that are multiple/many choice, where users can select Option A, B, C, D, and/or E, for instance. I am frustrated because when I download the Excel file, it will give me answers to that question that look like this:
A, C, E
A, B, C, E
Obviously, when I want to put this into columns in excel, I have to manually add blank cells between each answer. It would be much easier if Survey123 Excel output did this:
If it would automatically do this for me, in Excel I could have columns for each answer to the question. Is there a setting in Survey123 Connect that I am missing, perhaps, to get the output data to do this?
Survey123 creates the comma separated list according to the sequence that the user selects the options. I think for some people that would be the expected behaviour, for others it might be less so.
The other option is to use the functions in Excel to achieve this. There is an Excel blog which details this exact scenario:
Basically you can use the following formula to convert a cells comma separated values to be alphabetical:
- which assumes your data is in cell B5
Hello, I think maybe my issue wasn't understood. My problem is I'm NOT getting commas for the empty options. I would like to get commas inserted in the downloaded file for the empty options (which could be all the options, or just one, or none). Then I can use text-to-columns and not have to manually add empty cells and turn each answer into a Y/N.
Here's the specific question:
1. People: do you see evidence of:
I want to be able to easily take the Excel output from S123 and make it look like this:
|Location||toilet paper||trash||road||trail||bare soil||campsite|
But the way it comes out of the export doesn't allow me to easily use text-to-columns to convert to this format because there are not commas for the empty values. Only the checked values generate text and a comma...e.g.,
toilet paper,road,bare soil
which would give me:
|toilet paper||road||bare soil|
which I then have to manually add cells so it's all sorted by each issue.
Thanks for your help!
Thanks for explaining your scenario so well. I do however think you will be better off doing most of the heavy work in Excel.
You can achieve your requirement with the SEARCH function of Excel:
I hope this helps.
I have attached my Excel example for your convenience.