Table to 9^9 query header to table again - Gppgle sheets-CodePudding

I have this

CodePudding user response：

looking at your dataset it is not possible to go from A12 to A16. you will need to attach some unique symbols first and only then you can cut it with split fx

in A12 use:

=ARRAYFORMULA(QUERY(SUBSTITUTE(TRIM(A1:E9), " ", "♦"),,9^9))

in A16 use:

=ARRAYFORMULA(SUBSTITUTE(TRANSPOSE(SPLIT(FLATTEN(A12:E12), " ")), "♦", " "))

or full:

=ARRAYFORMULA(SUBSTITUTE(TRANSPOSE(SPLIT(FLATTEN(QUERY(SUBSTITUTE(
 TRIM(A1:E9), " ", "♦"),,9^9)), " ")), "♦", " "))