Splitting Multiple Choice (Checkboxes) Responses from Google Forms into Choice-Specific Columns
HTML-код
- Опубликовано: 15 дек 2021
- 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! 
Наука
Thank You! I was scratching my head with over 300 samples with multiple "Check All That Apply" .
THANK YOU! I've been trying to figure this out for weeks!
Great explanation. Easy to follow!
Boom! Just what I needed. :)
I really needed this!! Thank you so much.
This is amazing! Thank you - saves a lot of time.
you are legend! you save my life
Just excellent! Thank you very much!!!
Thank you. Clear easy to understand
Video really helpful I already watched more than 4 videos but this video is perfectly for what I want for my survey.
Thank you so much for this video. It really helps me a lot.
Thank you. It helped me a lot today. very grateful for you.
Thank you very very much... This video really save my life 🎉❤️
Hope all the good things happening to you.
Thanks from sudan🎉🎊
You are amazing. Thank you so much!
Thank you for this video!!
it was so helpful. Thank you for this.
Awesome!! Thank you.
SO nice. Thank you so much :)
Thank you so so much ... truly helped me on my client request.
Thanks it helped me a lot 😢please make more such videos
Thank you!
Thank you so much!
Madam you're the greatest for sharing this trick
Great resource
Thank you so much
thank you so much mam
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!
Very nice
too good
Amazing! How'd you show other options?
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.
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
@@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.
VERY NICE.....IN EASIEST WAY.
SACHIN
docs.google.com/forms/d/e/1FAIpQLSe0vOzBP6BVh1FZl5FDCZz4gf5ZwH2BwXhCy_V57ao52SvHAw/viewform?usp=sf_link
Please fill this form and thanks in advance
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?
Hi... the responses section gives a chart. Having the columns separate in google sheets or excel makes it easier to analyze on the go.
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?
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.
Thank you for an informative video! Is there a way to do this in google sheets (not in excel)?
Yes, the same formula holds in google sheets as well.
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.
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!!
Hi! Missing replying to you, I hope the formula worked for you by now :P
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😞
Hi! Missed replying to you! Check if you have an extra comma somewhere in the formula
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
Hi Rafael... couldn't exactly understand your query... could you send us a screenshot of your problem at kaisyn.consulting@gmail.com?
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 !
Can we complete this in Google Sheets? My friend did it one time but i forget how
Hi! Missed replying to you! Same formula in Google Sheets
Mam, how to make a bar chart or pie chart out of this data
Hi! You can make a bar chart with the total counts for each column. Pie charts don't work for questions with multiple select.
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.
-
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.
is not workinf for me, I did all the steps but excell told me i have to put an apostrophe before =if
Check if there are any spaces in your formula
Formula can't see....
You can try increasing the data quality for the video
Why I can't use the formula, I've already follow your step? :(
You could let us know where you're getting an issue and then maybe we can help :)
Same here. I am getting 1 in every cell
how to shift the exact response under the heading
Hi Praveen! Could you clarify your question a bit?
@@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
@@es_praveen5 That's what the video shows!! Do watch it completely!
Very nice Neha ji
docs.google.com/forms/d/e/1FAIpQLSe0vOzBP6BVh1FZl5FDCZz4gf5ZwH2BwXhCy_V57ao52SvHAw/viewform?usp=sf_link
Please fill this form and thanks in advance
It’s not working for me, please help 😢
Could you be a little more specific?
It would be nice if I could read the formula. I'm a dinosaur so not good creating formulas in excel.
@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)
@@kaisynlearning thank you.