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