google-sheetsgoogle-sheets-formulatransposearray-formulasgoogle-sheets-query

Formula to convert matrix to rows in Google Sheets

I'm trying to concatenate rows of a matrix in a spreadsheet, which I've got from an importhtml call.

For example, if I have a 3x3 table:

A   B   C
D   E   F
G   H   I

I would like a command/function to convert it a row like so:

A B C D E F G H I

The idea is that each line will have a different html form which the same table/data is meant to be extracted. This cannot be done manually each time.

Solution

  • replace A1:C3 with your IMPORTHTML formula:

    =SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(A1:C3),,999^99)),,999^99), " ")

    enter image description here

    if it contains words / spaces:

    =ARRAYFORMULA(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 SUBSTITUTE(A1:C3, " ", CHAR(127))),,999^99)),,999^99), " "))

    enter image description here