How to get IMPORTXML/IMPORTHTML result data in one cell?

Question

Working with Google Sheets, scraping from an html table like this:

I want to get all the rows in JUST ONE CELL... like this:

And I couldn't get the way to do it!

The expected result is to get all the table data in an unique cell... replacing columns division, just by a blank space and converting rows to simple text lines.

Any help, please? =(

NOTE: First values not always include de ":" character. Number of rows in table may vary.

Answer 1

try:

=INDEX(SUBSTITUTE(SUBSTITUTE(QUERY(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(
 your_formula_here
 ),,9^9)), " ", "×"),,9^9), " ", CHAR(10)&CHAR(10)), "×", " "))

Answer 2

Building from the previous answer you can try:

=QUERY(TRANSPOSE(ARRAYFORMULA(CONCAT(QUERY(TRANSPOSE(importxml_formula),,9^9),CHAR(10)))),,9^9)

Explaining the breakdown:

QUERY(TRANSPOSE(importxml_formula),,9^9)

This returns a table with one row with the number of columns as rows in the original table, with the result of each column is the data of each row in the table.

ARRAYFORMULA(CONCAT(QUERY(TRANSPOSE(importxml_formula),,9^9),CHAR(10))

Each column will be appended with CHAR(10), which corresponds to a line break.

TRANSPOSE(ARRAYFORMULA(CONCAT(QUERY(TRANSPOSE(importxml_formula),,9^9),CHAR(10))))

Transpose the table into 1 column, x number of rows, and finally the last QUERY merges the column into a single cell.

Sample Output:

Update:

Your IMPORTXML() already returned a single cell, and since they can be split by double space, you can use this formula instead:

=QUERY(ARRAYFORMULA(CONCAT(TRANSPOSE(SPLIT(IMPORTXML(A1,B1),"  ",,FALSE)),CHAR(10))),,9^9)