Splitting Multiple Choice (Checkboxes) Responses from Google Forms into Choice-Specific Columns

Skip right to the formula if you're already familiar with the Checkbox question in Google forms 6:33
Kaisyn's first video! We're a recent start-up that focuses on helping NGOs and students in the social sciences and education domain go digital. Read more about us in our "About" Section.
A lot of us may be including questions requiring multiple choice selections, otherwise known as Checkbox questions in Google forms. Unlike most other platforms, Google Forms currently (as of December 2021) doesn't give the ability to split multiple choices into separate columns. A lot of the answers out there involve using complex methods, formulas or just don't give the solution of separating the selected choices into specific columns.
In this video, we show a simple method involving only three steps to achieve the separation the way it is wanted.
The video shows both the often-followed path of using Text-to-columns and highlights its drawbacks, and the simple three-step method. If you're in a hurry, skip right to the formula part: 6:33
So let us know if this video helped you and what languages you'd want to see this solution in, and what other topics you'd like to learn from us.
We love feedback too!

[@90vit]

Thank You! I was scratching my head with over 300 samples with multiple "Check All That Apply" .

[@marcella7924]

THANK YOU! I've been trying to figure this out for weeks!

[@shopaholic_scholar]

Thanks it helped me a lot 😢please make more such videos

[@Johnny-zn1qg]

Great explanation. Easy to follow!

[@jdchee]

Boom! Just what I needed. :)

[@neeve77]

I really needed this!! Thank you so much.

[@abuhussam3590]

you are legend! you save my life

[@vaishnavibharucha7259]

Video really helpful I already watched more than 4 videos but this video is perfectly for what I want for my survey.

[@lilychu8912]

This is amazing! Thank you - saves a lot of time.

[@dodospanigirtzis]

Just excellent! Thank you very much!!!

[@sonyaunderdahl9517]

Thank you. Clear easy to understand

[@alhumairahassan1643]

Thank you very very much... This video really save my life 🎉❤️ Hope all the good things happening to you. Thanks from sudan🎉🎊

[@YasmeenEsam]

Thank you. It helped me a lot today. very grateful for you.

[@khornphengheng550]

Thank you so much for this video. It really helps me a lot.

[@rcwareagle]

Thank you for this video!!

[@BVRDMUSIC]

You are amazing. Thank you so much!

[@vidhi968]

it was so helpful. Thank you for this.

[@mattweld1290]

Awesome!! Thank you.

[@orvenbengala8313]

SO nice. Thank you so much :)

[@allygovin6242]

Thank you so so much ... truly helped me on my client request.

[@minanguyen3438]

Madam you're the greatest for sharing this trick

[@thekycklingwizard78]

Thank you!

[@officialmintt]

Thank you so much!

[@pravingurav106]

Great resource

[@oyohval]

Thank you so much

[@redowl247]

thank you a lot for this /cries. It does not work at first (also im using wps XD) but if you play around a bit, it does work! thank you!

[@informativegoogle]

thank you so much mam

[@jeremiahakinlabi7232]

Amazing! How'd you show other options?

[@SACHINKUMAR-jh7sv]

VERY NICE.....IN EASIEST WAY. SACHIN

[@05madavan58]

docs.google.com/forms/d/e/1FAIpQLSe0vOzBP6BVh1FZl5FDCZz4gf5ZwH2BwXhCy_V57ao52SvHAw/viewform?usp=sf_link Please fill this form and thanks in advance

[@raphaeldecourville4669]

Thanks for the useful video. One thing I'm missing is how do you deal with the "other" field like you show at 3:50. Is there a way to make a column that shows a 1 if the answer contains a custom text answer? Ideally I'd also like to make a column that contains the text of the answer as well.

[@kaisynlearning]

Thanks Raphaël. That's a tough one. Specifically because Google Forms do not give the custom answer appended by any specific string such as "Other -" . Hence the formula to retrieve a custom answer would be complex - but possible anyway. First you'd need to check which of the given options were marked, then compare their combined lengths and the number of expected commas+spaces with the total length of the response column. If they don't match then search for the unmatched length and use the RIGHT function to obtain the custom text from the response column. Google Forms has an Apps Scripts feature which can be used to get this function working within the form itself - it will have similar logic. So you could explore that. Or you could wait and pray that Google rolls out this much-much needed feature as a simple setting. :P

[@raphaeldecourville4669]

@@kaisynlearning Thanks for the detailed answer! I ended up writing my own App Script for exporting the form in the format I needed. I took a while and the export isn't as fast as Google's native one but it worked for my purpose.

[@sonakshikumari7209]

Very nice

[@florianawassong9756]

Hello Thanks for the tutoriel.. .. i have a problem.. i enter my data with cs pro i have make a checkboxfield so that reach participant can take more Thank one option….but the way its that when i transfert the file in spss the application cant understand it, for example spss suppose to understand that 1 is to …..,2 is to .. …,3 is to … ,etc … so when a participant choose may be 1,2 and 3 …spss understand 123 and not the value of 1.. ,of 2 and of 3… so i am really lost because i have more than 1000 participants . I aspect u can propose me a solution Thank you in advance !

[@user-eq4lg5fg1f]

too good

[@aquariushost7216]

Usually I just download the google form responses count for each option in the responses section of the google form itself. Is there an advantage in splitting into columns for analysis?

[@kaisynlearning]

Hi... the responses section gives a chart. Having the columns separate in google sheets or excel makes it easier to analyze on the go.

[@studywithPU]

Hey, I have a query. I have the following options 1. male friends and 2. female friends. When I apply the formula though the respondent has responded as male friends and not female friends, for some responses, it is showing value 1 in the female friend's column. Is there any possible solution for that?

[@kaisynlearning]

Hi, it's because one of the options is a part of the other (I think you mean the other way round - if the response is female friends only, then both male and female are marked as one.) In such cases you need to use an extra identifier in any one of the options - for example replace "female friends" by "female-friends" in both your data as well as your column headers.

[@life-long-learning4956]

Thank you so much for the explanation. Very informative video. I have one problem the function did not work for me... I am trying to understand why... Please help!!

[@kaisynlearning]

Hi! Missing replying to you, I hope the formula worked for you by now :P

[@anjapersson9004]

Thank you for an informative video! Is there a way to do this in google sheets (not in excel)?

[@kaisynlearning]

Yes, the same formula holds in google sheets as well.

[@coloredpencils01]

Yes there is! I just tried it in Google Sheets and the formula is exactly the same. Instead of 1 as the output value, I used $B2&" "&$C2 for the final part of the formula because my B column was first name and my C column was last name. That way, I could easily see the names of who had signed up for each column without scrolling back to the left.

[@rafaelagonzaless]

The answers box was empty after a new form was submitted, i need to manually highlight and drag again the box from C1, B2 to the new submitted form. Help me. Thank you

[@kaisynlearning]

Hi Rafael... couldn't exactly understand your query... could you send us a screenshot of your problem at [e-mail]?

[@chilanayhajiyeva2208]

Hi, thank you for the video! I was trying to apply the same formula for my responses, but at the end it just says that I have entered too many and I cant see "1" per each answer😞

[@kaisynlearning]

Hi! Missed replying to you! Check if you have an extra comma somewhere in the formula

[@williamfanning555]

Can we complete this in Google Sheets? My friend did it one time but i forget how

[@kaisynlearning]

Hi! Missed replying to you! Same formula in Google Sheets

[@nohson1]

We are trying to convert a multiple choice questions document into excel, but it's not working as expected. The questions are formatted as follows: 1. What is the name of the first man? A. James B. John C. Adam D. Prince We want to import it into excel so the question appears in a column, the same as each option. For example, Column A Column B Column C Column D Column E What is the name....? James John Adam Prince Please help us with a short video or explanation of how to achieve this. -

[@kaisynlearning]

Hi! You could use the transpose function while pasting it in Excel (under Paste select the transpose option) but you'll need to do that for every question manually. The only other way possible is to design a macro.

[@christelleolibrice7690]

is not workinf for me, I did all the steps but excell told me i have to put an apostrophe before =if

[@kaisynlearning]

Check if there are any spaces in your formula

[@srijadutta4810]

Mam, how to make a bar chart or pie chart out of this data

[@kaisynlearning]

Hi! You can make a bar chart with the total counts for each column. Pie charts don't work for questions with multiple select.

[@es_praveen5]

how to shift the exact response under the heading

[@kaisynlearning]

Hi Praveen! Could you clarify your question a bit?

[@es_praveen5]

@@kaisynlearning when a user fill a Google form and chooses multiple choices and that Data should be exact Under the same heading in the sheets or Excel

[@kaisynlearning]

@@es_praveen5 That's what the video shows!! Do watch it completely!

[@aarsheyshah9485]

Formula can't see....

[@kaisynlearning]

You can try increasing the data quality for the video

[@mochammadabdulmachfud5847]

Why I can't use the formula, I've already follow your step? :(

[@kaisynlearning]

You could let us know where you're getting an issue and then maybe we can help :)

[@neha12181]

Same here. I am getting 1 in every cell

[@ramrattankaushik3104]

Very nice Neha ji

[@05madavan58]

docs.google.com/forms/d/e/1FAIpQLSe0vOzBP6BVh1FZl5FDCZz4gf5ZwH2BwXhCy_V57ao52SvHAw/viewform?usp=sf_link Please fill this form and thanks in advance

[@teddahrable]

It would be nice if I could read the formula. I'm a dinosaur so not good creating formulas in excel.

[@kaisynlearning]

@teddahrable thanks, we'll try to zoom in on the formula part in our next videos. You could also try going for full screen at a higher video resolution incase the formula seems to be blurred. For now here's the formula we've shown: =IF(ISERROR(SEARCH(C$1,$B2)),"",1)

[@teddahrable]

@@kaisynlearning thank you.

[@g-4rodrigojrclarito758]

It’s not working for me, please help 😢

[@kaisynlearning]

Could you be a little more specific?