Google Sheets Query and Comma Separated Data

0
votes

I have a simple data query in a Google Sheet:

=query('2019'!$A$2:$A,"select A")

The data in column A has multiple cells that contain comma-separated values. Only the data in the first cell (A2) returns with all of the comma-separated values. How do I get all of the data returned in this query?

2
google-sheetsstring-formattinggoogle-sheets-formulaarray-formulasgoogle-sheets-query
share a copy of your sheet with example of desired output – player0

2 Answers

1
votes

It's very likely the problem is cause because the data without commas are of ty numbers, boolean, dates, time, duration, in other works, they are not text values.

This happens because QUERY assigns a data type for each column based on a sample of each column data. If the column includes data of different type, they aren't included.

The way to solve this is to prior adding the data to QUERY convert all the column values to the same data type, in this case all should be text. To force that all the values are treated as text you could preppend and apostrophe / single quote. Other methods are

  • set the cell number formatting to plain text
  • concatenate the cell value to an empty text "" like =A1&""
0
votes

try like this:

=ARRAYFORMULA(QUERY(TO_TEXT(2019!A2:A), "select A", 0)