Survey123 data - commas for blank options?

Question

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?

SarahTruebe · Answer

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

DeonLengton · Answer

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");"")

I hope this helps.

I have attached my Excel example for your convenience.

Sarah.xlsx

SarahTruebe · Answer

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 soilcampsite1Y 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!