Survey123 data - commas for blank options?

288
4
10-04-2021 03:12 PM
SarahTruebe
New Contributor II

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:

A,,C,,E

A,,B,C,,E

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?

4 Replies
DeonLengton
Esri Contributor

Hi Sarah

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.

Regardless, there are two options to get the list sorted. The first option is to use a JavaScript function in your survey to sort the list alphabetically. As far as I know this will require a logged in user so doing that with a public survey wont be an option.

Nonetheless, you can look at something like this to sort the values in JavaScript then just save the output to a new field in your survey:

str.split(",").sort().join(",")

The other option is to use the functions in Excel to achieve this. There is an Excel blog which details this exact scenario:

https://exceljet.net/formula/sort-comma-separated-values

Basically you can use the following formula to convert a cells comma separated values to be alphabetical:

=TEXTJOIN(",";1;SORT(FILTERXML("<x><y>"&SUBSTITUTE(B5;",";"</y><y>")&"</y></x>";"//y")))

- which assumes your data is in cell B5 

 

SarahTruebe
New Contributor II

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:

  • toilet paper/human waste
  • trash
  • road
  • trail
  • unnatural patches of bare soil
  • campsite

I want to be able to easily take the Excel output from S123 and make it look like this:

Locationtoilet papertrashroadtrailbare soilcampsite
1Y Y Y 
2YYY  Y

 

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

toilet paper,trash,road,campsite

which would give me:

Location      

1

toilet paperroadbare soil   
2toilet papertrashroadcampsite  

which I then have to manually add cells so it's all sorted by each issue.

 

Thanks for your help!

0 Kudos
DeonLengton
Esri Contributor

Hi Sarah

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:

=IFERROR(IF(SEARCH(F$4;$E$5);"Y";"N");"")

DeonLengton_0-1633503665740.png

I hope this helps.

I have attached my Excel example for your convenience.

0 Kudos
SarahTruebe
New Contributor II

Thank you! This should work great for all questions that we have like this. Thanks for your help!

0 Kudos