Google-sheets – How to make a chart read data in a cell that’s separated by comma in Google Sheets

googlegoogle sheetsgoogle-forms

Whenever I create a chart, it reads all the data in a cell all-together, instead of respecting each individual value in a cell.

I made a Google Form were people can choose multiple options. The information is stored on one sheet, and the charts are displayed on a second sheet.

Here's My spreadsheet:
https://docs.google.com/spreadsheets/d/1M-JANPRjKn_nkiskWPLfl-UJU1yIUftrTKEGcN39jBs/edit?usp=sharing

Best Answer

I've added two sheets to your sample spreadsheet: "FormQ3" (short for "Form - Question 3") and a duplicate of your Charts renamed "Charts-Erik."

First, never mess with raw form data. It is standard practice to set up a separate sheet or sheets (e.g., my FormQ3) to handle processing data.

If you've set up your form to send over multiple, comma-separated answers, Sheets just sees those as one long string. Consequently, charts can only see them as solid strings.

FormQ3 holds one array formula in A1 that separates out the comma-separated lists, along with a few added "bells and whistles" to deal with anomalies:

=ArrayFormula({"Alt Games List";QUERY(IF(LEN(FLATTEN(TRIM(SPLIT(FILTER('Form Responses 1'!C2:C,'Form Responses 1'!C2:C<>""),",",0,1))))<=25,FLATTEN(TRIM(SPLIT(FILTER('Form Responses 1'!C2:C,'Form Responses 1'!C2:C<>""),",",0,1))),"Other"),"Select * Where Col1 Is Not Null")})

Working from the inside out, first Column C of your raw data is FILTERed to only receive non-null rows. SPLIT splits these at the comma. TRIM removes any extra spaces that would have existed after commas. FLATTEN (which is an as-yet-undocumented Google Sheets function) takes all of these and forms one column from them. IF checks to make sure that the LENgth of each of these is not more than 25 characters (which I figured was safe for any real game name, but would rule out phrases like one person put in there); IF the LENgth is <=25, the processed game name is added to the list (which is why you see a big block of the formula repeated within itself; it just means "list the processed version"). Anything going over 25 characters is figured not to be game name, so it will just list "Other." Then, a QUERY is run on all of that to get rid of any remaining blank rows in memory.

This list is then used to create your second chart (which you can see in "Charts-Erik").

BTW, if you wanted to be hands-on and go through every submission to cull out game names from long phrases (like your raw data C4 where someone added "If you know of the game Stormworks."), you can just directly edit the raw-data sheet to replace "If you know of the game Stormworks." with "Stormworks" in the list. This way, that game name would be added to the FormQ3 list and the chart. You can't, however, edit the FormQ3 list directly, because it is formula-created. If you try typing in the list, you will break the formula and wind up with an error.

Related Topic