Transpose, Split, and Join Google Sheets

0
votes

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.

3
joingoogle-sheetssplittranspose

3 Answers

0
votes

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.

1
votes

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.

0
votes

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 :)