Exporting survey results to Excel and count the selected options

  • Profile Image
    squishypeanut
    Asked on February 06, 2018 at 06:43 PM

    Hi there!

    We had a choice field on our survey where respondent could choose multiple responses. Is there a way to see that data in an easier format? Like how many people chose each option, for instance?

    The Excel export is making it tricky to sort that data. Or any Excel tips to sort the data. Fields don't have any kind of special delimitation between them. There are no commas or anything to tell the data how to split up. 

    Thanks!

     

  • Profile Image
    EltonCris
    Answered on February 06, 2018 at 11:34 PM

    Unfortunately, they're configured to display in that manner on Excel. However, you can try the following workaround on how to place them into separate columns using excel formula.

    Actually, you can add delimiters on each option per line since each option is brought down into new lines. You can use the following formula. This will add ^ symbol as a delimiter on each option per line. You can drag down to automatically apply it to the other cells.

    =SUBSTITUTE(A1,CHAR(10),"^")

    After that, highlight the column containing the formula, then go to Data --> Text to Columns in your Excel app. When the Wizard comes up, select Delimited on the first screen and Other on the next screen. In the blank space next to Other, insert ^. Click Finish, and your products should be listed in separate columns. 

    If you need further assistance, let us know here.