0

I have a Google Sheet linked to a Google Form. In the second page I added a formula to clean up the data, since one of the questions allows to chose multiple options and it results in a column with these options separated by comma, in the same column. Therefore, I put a formula in the second page taking this info and separating it into multiple columns:

=ARRAYFORMULA(TRIM(SPLIT('Form responses 1'!H1,",",TRUE,TRUE)))

The problem is that each time a new reply is added by the form, a new row is added to the first page, and then I have to add this formula by hand for the new row.

My question is: Is there any way to do it automatically?

The alternative is to add the formula to all the rows, and allow it to reply with #VALUE! if the row in the first page is empty.

Here is a running example of it: Google Sheet example

Improve this question
8
  • 1
    I see you have now share a spreadsheet. This is a good start. But this sample spreadsheet shows only one column of data coming in through the form (i.e., 'Form responses 1'!A:A). A simple solution can be made for this one column of data. But if your real form is sending more than one column of data into the raw data sheet, you will have many problems. So I will ask: do you really only have this one column of data coming in from your form? If so, I will share a simple, one-formula solution.
    – Erik Tyler
    Jun 17, 2021 at 13:52
  • Thank you Erik. I have added more columns. In any case, the column that I have to parse, have mandatory data (cannot be empty). The others may be empty or not.
    – Alejandro Díaz-Caro
    Jun 17, 2021 at 15:03
  • Is the first column the only one that may have multiple answers that need to be separated? And how many columns of data are coming in from your form to 'Form responses 1' on your actual spreadsheet?
    – Erik Tyler
    Jun 17, 2021 at 18:38
  • You got the "simple" answer from doubleunary below. However, this will not account for your additional columns. If you'd like to reply and answer my to questions above, I am happy to pass along a formula that will account for everything, not just Col A. But I can only do that if I have the full information. On the other hand, if you are all set with what you have, great.
    – Erik Tyler
    Jun 17, 2021 at 19:47
  • Thank you Erik. I only have one column to parse.
    – Alejandro Díaz-Caro
    Jun 17, 2021 at 22:15

3 Answers 3

Reset to default
1

Wrap the split() in an iferror(), like this:

=arrayformula( iferror( split( 'Form responses 1'!A1:A, ", ", false, true) ) )

See the new Solution sheet in your sample spreadsheet.

Improve this answer
1

Based on as much as I know, this is what I can offer:

=ArrayFormula(FILTER({TRIM(SPLIT('Form responses 1'!A:A,",")),'Form responses 1'!B:C},'Form responses 1'!A:A<>""))

Adjust the :C in order to reflect your own furthest-right column.

Improve this answer
0

I have found a workaround. To fill all the column with

=IF('Form responses 1'!H1="","",ARRAYFORMULA(TRIM(SPLIT('Form responses 1'!H1,",",TRUE,TRUE))))

So it checks if the row is empty or not

Improve this answer
2
  • That will work with your sample spreadsheet, but not with the real form responses sheet. Form responses are always inserted in newly created rows that cannot be referenced directly in advance. The usual recipe to work around the issue is to use an array formula that processes a whole column in one go, instead of processing one cell at a time.
    – doubleunary
    Jun 17, 2021 at 15:45
  • In fact, I tried and it worked, but because I copied this code on any single cell of the column in the second page, which is quite pedestrian. Your solution is perfect. Thanks!
    – Alejandro Díaz-Caro
    Jun 17, 2021 at 16:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.