This week I helped a client realize there is a very simple way to take the output of a multiple selection choice and split the values into separate columns.
(Note: he’d paid someone to create a zap to do the same thing because he didn’t know about Google Sheets’ “split()” function)
Scenario: you have a multiple-select multiple choice question that throws a string of the responses into one column - each choice is separated by a comma.
The image above shows the Results in Typeform. The image below shows what lands in Google Sheets once you have Connect-ed the typeform.
Note that the selections show up as a comma separated list.
What’s the trick to separate these inputs into separate columns so I can pull them into a report more easily?
In the Google Sheet, I create a ‘reporting’ tab/worksheet so that I have control over the column headers - I like to make them simple so that they are easier to pull into the output template.
Here’s the tab that the typeform uses:
Now what I want to is create the Reporting tab to look like this:
Note that I have a column for the email address then choice 1 through 10. I created the column headings manually but Google Sheets does the splitting of the data for me.
Here’s the formula in cell A2 to pull in the email addresses from the main tab:
=ARRAYFORMULA('split test'!A2:A)
It simply looks over into the main tab, into column A and pulls in every row as it is added from the typeform submission.
The formula to in B2 to do the ‘splitting’ of the data string is as follows:
=arrayformula(if(isblank(A2:A), " ", split('split test'!B2:B, ",")))
The formula does a check to see if there is a value in the email cell on a given row. If there is no email it shows an empty field. If there IS an email, the split() function looks over in the main tab to grab the comma separated list in column B and then splits the data and writes it to the appropriate columns - based on the order in the string of choices.
There are NO formulas required in cell C2, D2 etc. - the split handles putting the data into the columns.
⭐ Here’s the video of the solution
⭐ Here’s the typeform - give it a try
⭐ If you want to see the Google Doc with the/more details click here
cheers
des
