Transform comma separated google form answers to multiple lines in spreadsheet

0
votes

I have made a google form to which some answers are formatted as comma separated strings inside the automatically populated google spreadsheet. I would like to read from this sheet to another sheet and reformat the answers so that each comma separated answer is shown on a new row. I have tried to apply an ARRAYFORMULA that reads from the original sheet and then use a solution that uses SPLIT and TRANSPOSE the cell content, however combined with the ARRAYFORMULA this fails since it would overwrite contents in other cells.

Here is an example spreadsheet with the responses, a solution sheet, and a desired results sheet. https://docs.google.com/spreadsheets/d/1r_l5fVJ9lGfpubO2o3pXicV7JlZWmANjwSgNi7_DL0A

Any suggestions for how I can achieve the end result?

1
google-sheetsgoogle-forms
Are you willing to consider an Apps Script solution? @Kajsa – ale13
@ale13 I would have preferred not to as it's not going to be a long term use form but I can if that's necessary. – Kajsa
I know that this can be done with a formula by some of the top experts here on SO. I've just seen this question now, and will see if I can come up with something while you wait for them to find this... – kirkg13
Can you tell us how many questions there are, since this affects how many columns the formula may be acting on? – kirkg13
@kirkg13 I'm afraid I don't now how many questions there will be in the end. – Kajsa

1 Answers

1
votes

Okay, I assume this isn't really what you want, but visually it looks okay... Try this formula:

={{'Form responses'!A2:A3},ArrayFormula(regexreplace('Form responses'!B2:E3,", ",CHAR(10)))}

Then format the cells so that the cell contents are TOP-aligned, instead of the default BOTTOM-aligned.

Realistically, I imagine that you want each question answer split into multiple cells. But if your data responses really contain letter values separated by commas, as you've indicated, you can still search through those cells to find whether an answer contains a certain value. It all depends on why you want the results structured the way you do.

If you can clarify what you want to do with the form results, instead of just appearing vertically for each question, perhaps we can provide a full solution for that requirement?

enter image description here

UPDATE1:

Okay, I may be getting close. I can get your data transformed to look like the following: enter image description here

This would let you do the analysis that you want, by searching for Q.1 (question 1 responses) in the first column, and then all the answers in the third column, along with the owner in column 2. And from this, it will also definitely be possible to put the results in the exact form you want. It just may take an intermediate step.

UPDATE2:

Okay, I think I have something you can use. I can convert your data to either of the following two layouts.
enter image description here

The one on the right is closest to what you asked for, with the exception that the answers on the right are bottom aligned, with blanks above. But you can still process them for analysis, with queries. I honestly think having the user identifier (email address) on each row would make things simpler, but I can provide it either way.

The layout on the left is more of a traditional database layout, and would make analysis very simple. Each row has the date and email identifiers, the question number, and the answer (or one of the answers) to that question, from that user.

If this is helpful, it might be best if you enabled your sample sheet to allow us to edit it, to enable me to implement it in your sheet. But here is my sample sheet, in case anyone wants to look through it. Note that the main formula to reformat the data, in Solution!B3, could benefit from a lot of cleanup, and is probably nowhere near the best way to achieve this. Just throwing up one possible solution...

I'll try to add some explantion for the formula at some point, but ask if you have any questions.