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

Lebene

New Member
Joined
Sep 20, 2019
Messages
40
Platform
  1. Windows
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?
 
Last edited:

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Lebene

New Member
Joined
Sep 20, 2019
Messages
40
Platform
  1. Windows
Hello, someone helped me with the formula and I modified it to remove the quotation marks and the square brackets:
Excel Formula:
=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).
 
Last edited by a moderator:
Solution

Watch MrExcel Video

Forum statistics

Threads
1,123,252
Messages
5,600,543
Members
414,386
Latest member
PARAMATHMA SENTHILNATHAN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top