I'm a google sheets beginner and need some help. I am using a sheet that is populated by a google form, so it's a bit tricky for me.
The google form allows users to select up to three options - red, blue, green. They must select one and can select three, so the output in column A can be "red" or "red, blue" or "red, blue, green" or any combination.
I've figured out how to split the answer combination using the formula =ARRAYFORMULA(trim(SPLIT(A2, ","))) so I get the individual outputs without the commas (that the google form returns) across up to three columns.
But - since it's a google form, I also need to have this formula automatically pasted as new rows are added to the sheet from the form. I can't figure out how to do a "double" array formula (for additional columns for data output and additional rows as data input) or if that's even an option or if I'm missing something here. Any help? thank you!
use in row 1:
={"formula", "header", "row"; ARRAYFORMULA(TRIM(IFERROR(SPLIT(A2:A, ","))))}
or in row 2:
=ARRAYFORMULA(TRIM(IFERROR(SPLIT(A2:A, ","))))
fully dynamic formula for row 1 would be:
=ARRAYFORMULA({"fx", IFERROR(REGEXMATCH(TRANSPOSE(ROW(
INDIRECT("A2:A"&COLUMNS(SPLIT(A2:A, ","))))))); IFERROR(SPLIT(A2:A, ","))})