How to split Google Form™ checkbox responses into separate columns

A checkbox question puts every ticked box in one cell. Here is how to turn that cell into one column per option, without touching the tab Google Forms™ writes to.

What Google Forms™ actually writes

For a checkbox question (“tick all that apply”), Forms joins the ticked options into one cell, separated by a comma and a space, in the order they appear on the form:

That is readable one row at a time, but a filter on “Gluten free” misses Bob, a pivot table treats Vegetarian, Gluten free as its own category, and a plain COUNTIF undercounts. You want one column per option instead.

Don't use Data › Split text to columns on the responses tab

It is the first thing most people try, and it has three problems:

• It writes over the columns to the right. On a responses tab, those are your other questions. The split overwrites them.
• It splits by position, not by option. Alice's “Vegetarian” lands in the first new column, and so does Chris's “Vegan”. No column means one thing.
• It is a one-off. The next response arrives unsplit.

The SPLIT function has the same position problem. It also has a trap of its own: =SPLIT(D2, ", ") splits on each character by default, so “Gluten free” becomes “Gluten” and “free”. If you use it, pass FALSE as the third argument so it splits on the whole ", ".

The right shape: one TRUE/FALSE column per option

Add a new tab (call it Diet) and leave the responses tab alone. Put the option names across row 1, starting in B1: Vegetarian, Vegan, Gluten free. In A1 type Name, and in A2:

=ARRAYFORMULA('Form Responses 1'!B2:B)

Then in B2, and fill it right across the option columns:

=ARRAYFORMULA(IF('Form Responses 1'!$B$2:$B = "", ,
  ISNUMBER(FIND(", " & B$1 & ",", ", " & 'Form Responses 1'!$D$2:$D & ","))))

Each cell is TRUE when that person ticked that option. Because the formula looks at whole columns, new responses are split as they arrive.

Why the extra commas. Wrapping both the answer and the option in ", " and "," means an option only matches as a whole item. Without them, “Vegan” would also match inside “Vegan-friendly”. We use FIND rather than SEARCH because SEARCH treats ? and * in an option as wildcards.

Where the formula still goes wrong

• Options that contain a comma. If one option is “Tea, coffee” and another is “Coffee”, a person who ticked only “Tea, coffee” also matches “Coffee”. The fix is to match the longest option first and remove it before looking for the shorter ones. That is awkward in a formula and easy to forget.
• “Other” answers. Whatever someone typed under Other (Chris's “no nuts”) lands in the same cell. It matches none of your option columns, so it silently disappears unless you add a column that catches the leftovers.
• New options. Add an option to the form and nothing in your header row knows about it. Those ticks are invisible until you notice and add a column.
• Renamed options. Rename “Gluten free” to “Coeliac / gluten free” and the column stops matching the new answers. Old answers keep the old wording.

If you only need counts rather than a column per option, see counting checkbox answers. It uses the same matching trick in a single formula.

More on Google Form™ responses

• Count checkbox answers from a Google Form™ (COUNTIF on multi-select)
• Remove duplicate Google Form™ submissions, keeping the latest
• Google Form™ responses: shorten long question headers without breaking the form
• Phone numbers losing the leading zero in Google Form™ responses
• Why you shouldn't sort or edit the Form Responses tab (and what to do instead)
• The guide: every rule, with the by-hand formula

Google, Google Forms™, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. Form Response Cleaner is not affiliated with or endorsed by Google.