Splitting multiple choice entries from Microsoft Forms in Excel without text to columns

[Lebene]

Good morning all,

I am using power automate to automatically populate a spreadsheet with results, those results would then be fed into a pivot.

You might be asking "why not just use Forms' visualisations?", well the person wants to only view submission results week by week and might get asked on different time periods, which they won't be able to filter in Forms. Currently, they are creating a new form every week!!

TL;DR
Is there a formula that can detect the 12 options and the "Other" options to split across? Someone might only pick one option, one might pick 6, I doubt anyone would pick all 12 and then give an "other" reply.

I don't want to use text to columns as I want it to be as low maintenance/touch as possible, the person using the form/spreadsheet is a bit tech-phobic. And I'm only doing a favour as it hurts me to think they are duplicating forms week-by-week for this info.

I don't know if it's a combination of len, substitutes and array formula. In google I can do =SPLIT(cell,";") but don't know the Excel equivalent.

Any help is appreciated.

edit: I saw a suggestion for having a UDF which replicates =SPLIT function in Excel? How could I do that?

 

[Lebene]

Hello, someone helped me with the formula and I modified it to remove the quotation marks and the square brackets:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($E2,",",REPT(" ",800)),"""",""),"[",""), "]",""),800*(COLUMNS($E1:E4)-1)+1, 800))

Basically, E2 has the cell which is automatically populated by Power Automate. You make several extra columns which exceed the maximum possible selections to pick, then drag the formulas across. So if you have 8 options in multiple-choice, make 8 extra columns next to the auto-populated one.

The '800' is the character count, I increased it to 800. If your answers are longer than 800 characters, just increase it but there is a limit (choices shouldn't be so long you'd reach the limit anyway)

As I had two questions with multiple answers, I made two tables. Question8 and Question9, for example.
My auto-populating form has 'Submission Date, Submission Date, Submitter Email, Quest 1, 2,3....' etc.

So, create two Power Query on each table:

1. First make one of the Submission Dates only show Date, and the second to only show Time (Microsoft sends through submission as 'dd/mm/yyyy hh:mm' or something like that).
2. I unpivot all the columns with the multiple-choice question answer to make it back to one column only.
3. Now remove duplicates and filter out blanks

You can now create charts or pivot tables with unduplicated entries for each person. I just hide the table and query tables and open up to refresh ('Refresh when I open the file' isn't working for me).