Your browser version is outdated. We recommend that you update your browser to the latest version.

Using Google Sheets "split" function to separate your typeform's multi-select multiple choice output

Posted 1/15/2024

Happy New Year folks. 

I hope that you and yours had a great end of year celebration - regardless of HOW or WHY you celebrated!

Ok down to work - a short tip 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?

 

 Like this example:

Let’s do a quick review of the typeform to set the stage - you can try out the working version at this link.

 

Here’s the structure:

 

We ask for a valid email to do the test output - it just makes it easier to do this and tie it into Document Studio to send out the custom report. 

 

We’ve got 15 choices on this question - users are required to select a minimum of 3 and a max of 10 (see the Range identified on the right hand side) 

Because we allow for the multiple selection, the output for this question is stored in the comma separated fashion in the Google Sheet. 

I stipulated a max of 10 selections - that is why you’ll see that I create column headings for 10 possible choices in the Google Sheet in a minute. 

The third question in the typeform is simply a Statement page to show the selections in the comma-separated format. 

 

There are no variables or logic created for this form - other than making the questions required. 

Now for the Google Sheets work!

Note that I have already shown you the structure of the output template that I created in Google Slides. You’ll need to create your output template at some point - I recommend creating the draft structure of the report before you head to the Google Sheet and creating the Reporting tab details. 

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. 

 

Now all the columns will be populated with the values - from the first three columns (minimum from the typeform) up to the 10 selections maximum. 

That should make it much easier to get the data from a multi-select multiple choice into separate columns AND into your reports. 

I made a wee video with the details for your viewing pleasure - click here to watch

And the Google Doc is here

That’s all for this week. 

Have a great week. 

 

des