Using Google Sheets "split" function to separate multi-select multiple choice responses into individual columns | Community
Skip to main content

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. 

 

 

YfWohTyEzHzjzBSemqAP27D94mQa71be7bJZnyN8rFZjmL57gHqU4pBLrT8GjLY5wabDxFC1JBVodoJPlyQab63MyPX9LZTkdz73MVN2LDtCml32mrTv4AimVurINcOUUn0MQNB5r7ytnOaZB4fZ-g

 

 

The image above shows the Results in Typeform. The image below shows what lands in Google Sheets once you have Connect-ed the typeform. 

 

Uejsx5A0es9BPhJ7d6_hUvlRX_uaemj9YfYMTA7P9y5ErFSnovizZkAT-o3LAuHUa4BxDqEISORgTlfiefZSeiCA3xiq5mwLt5lqldcaDgDaKSNzjQJFhnSEgnbuQzAb1kN4yns7sLqrDSBpBV_ecA

 

 

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: 

 

Uejsx5A0es9BPhJ7d6_hUvlRX_uaemj9YfYMTA7P9y5ErFSnovizZkAT-o3LAuHUa4BxDqEISORgTlfiefZSeiCA3xiq5mwLt5lqldcaDgDaKSNzjQJFhnSEgnbuQzAb1kN4yns7sLqrDSBpBV_ecA

 

Now what I want to is create the Reporting tab to look like this: 

 

aLVFT9k90MDaZU5qZO_N7Wurrxu33nQ8GYHqvnkgEC3X6qe1dhSHLhYEMAlvv3suJu1EWK75N58-RohW487r1w9TO9uvHcLZTvwjzfCWkbSadwlkGVKmAMy2XL-lwji9YhCaznB1VUw3tlBM-7xYJw

 

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, ",")))

 

lGTYzMn8-gP15wT1tIfmVgW08GLPv1Llmh2cGg4NarnuXXP5Ywoe_oo5Wf45N6OzY_H7xf4_UBCeCN1NmS_SuyX-pDYyN1oZQz2T9CyKwhCOEpuOO2tpTfBTrhMKVAYJJrX-dy6nTBkA0ZhlfGwOlw

 

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

Wow @john.desborough what kind of sorcery is this?!! I didn’t know this was possible, and have never come across the split function in Excel. Thanks for enlightening us ✨

 

 

@James - dude… not sorcery, simple ‘mother of invention’ lol…

try this in excel: =TEXTSPLIT(text,col_delimiter,_row_delimiter],dignore_empty], rmatch_mode], tpad_with]).

now the only thing that is NOT my forte in excel is the equivalent to the Google Sheets arrayformula() function…. to automatically populate a ‘reporting tab’ …. just sayin’

 

des

This efficient use of the Google Sheets "split" function streamlines the process of breaking down multi-select multiple choice responses into distinct columns. This not only enhances data organization but also simplifies analysis, offering a practical solution for managing diverse survey inputs. Kudos for leveraging this feature to improve spreadsheet functionality!

This is great John!

Any ideas on how to deal with scenarios where the choices themselves contain a comma?
For example, I have this in a cell:
Action Supplier Sourcing, Execute RFI, RFQ, RFP, Define Categories

Where Execute RFI, RFQ, RFP is actually a single selection in Typeform, but the split function in Google shets of course treats it as several selections.


I’ve been looking for a setting to change the delimiter in the google sheet connection/export, but haven’t found anything useful.

 

I know I can re-word the selections to not contains commas, but was hoping to avoid that.

 

Can I also offer a small improvement to your formula? You end up with a space in front of every selection except the first one, because the typeform delimiter is actually comma + space.

You can use the SUBSTITUTE function in google sheets to remove the unwanted spaces before passing it to the SPLIT function on-the-fly:

=split(SUBSTITUTE('split test'!B2:B ,", ",",") , ","))

@Jorgen76 if you want to use split() you should probably seek to avoid using commas in your choice string 

maybe something like Execute RFI - RFQ - RFP where no commas are in the string. 

or put a semi colon at the end of each choice string and have the split function use that as the delimiter.

des

Thanks @john.desborough I ended up doing exactly that in this case. Will keep it in mind for future form designs.

Reply


Still need help?

Don't be shy and ask the community.

Ask your question here!
Community T&Cs