0

I am using Google forms so that the user inputs a list of 6 digit numbers. I need to transfer those numbers to another Google Sheet but before I do that, I would like them to be in a Column on the responses Google Sheet. I know I have to Transpose, Split based on ",", and Join all responses since this will be done daily. So far I've tried:

=TRANSPOSE(SPLIT(JOIN("," B2:B)B2:B, ",", TRUE, TRUE)B2:B)

but I'm getting a parse error.

Here is a link to the test page I'm using: https://docs.google.com/spreadsheets/d/1n5ZyTThvulFxjb274qpzytgiplzve16DG4z8WPlqGoU/edit?usp=sharing

Any help is appreciated.

Rudy Acevedo
  • 23
  • 1
  • 6

3 Answers3

1

Formula parse error means that your formula is incorrectly typed. In your case, try adding a few commas between arguments.

More here for example: https://www.benlcollins.com/spreadsheets/formula-help/#errorError

Once this is fixed, you'll need to look over your formula again to make sure it works.

a-burge
  • 1,535
  • 1
  • 13
  • 25
0

Use this formula in cell C2

=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(JOIN(",",(FILTER(B2:B,NOT(ISBLANK(B2:B))))),","))))

This is a dynamic formula which • Filters out blank values from B col • trims all value to convert them into integer • data will be populated automatically in C col

Hope this helps :)

Shrvan D
  • 69
  • 4
  • This works! thank you! only thing is I noticed if the first digit is a 0, it does not come out in the new column created. So for example, when I plugged in your formula, this came out: 123456 123456 122222 123455 123454 123456 123455 98765 567890 . Skipping the 0 in 098765 – Rudy Acevedo Sep 10 '18 at 21:29
0

Here use this upgraded formula

=TRANSPOSE(SPLIT(REGEXREPLACE(JOIN(",",(ArrayFormula(" "&TRIM(FILTER(B2:B,NOT(ISBLANK(B2:B)))))))," ","'"),","))

any data handling tool wont let you keep a '0' as the beginning charater of a number i.e. if you put =01110 in any cell,it would yield 1110.

But if you convert the same into a string the desire output can be obtained i.e. if you put ='01110 in any cell,it would yield 01110. Notice the " ' " single quote character(numerical to string).

I have just upgraded the formula to produce a string output of these QR code. Hope its fine by you.

Shrvan D
  • 69
  • 4