Query function returning blank values instead of text in column with both text and numbers [duplicate]

Question

I'm using this formula: =QUERY(A1:M;"SELECT D")

COLUMN D contains rows with numbers and others with text strings, but the QUERY is only returning the numbers and the text strings are showing as blanks.

I need both data types returned.

Answer 1

I believe your format may need to be changed.

Try this: =query(table!A1:M, "select D")

This is assuming that "table" is the name of the sheet with data.

If table is a named range, then you either need to use that or A1:M as query only accepts a single range to search.

Answer 2

FILTER & LEN

=FILTER(D:D; LEN(D:D)) 

Explanation

• FILTER column D where the value has a LEN (character length) greater than zero
• FILTER allows more flexibility because it doesn't change the data types.

QUERY w/ ARRAYFORMULA

=ARRAYFORMULA(
   QUERY(TO_TEXT(D:D)))

# equivalent formula
=QUERY(
   ARRAYFORMULA(TO_TEXT(D:D)))

Explanation

• The QUERY function recognizes only a single data type per column.
• If there are multiple data types (eg. text and numbers) only one will be recognized.
• The solution uses TO_TEXT to change all numbers to text strings in an ARRAYFORMULA.
• This approach (unlike FILTER) runs the risk of problems if results are used in future calculations.

QUERY w/ ARRAYFORMULA, LET, IFERROR

=ARRAYFORMULA(
   LET(q, QUERY(TO_TEXT(D:D)),
       IFERROR(1/(1/q),q)))

Explanation

• The LET function stores the result of the QUERY w/ ARRAYFORMULA formula in the named variable q
• When performing math on a text string Sheets will "try" to interpret it as a number and this is used to try to coerce the text strings in q to numbers.
• Dividing 1 by the inverse of a non-zero number n will return n. If n is anything other than a non-zero number, Sheets returns an error.
• In this way within the ARRAYFORMULA all q that can be are coerced to non-zero numbers.
• IFERROR returns the original text string from q if q couldn't be coerced to a non-zero number and instead triggered an error.