Query is ignoring string (non numeric) value

Question

I have this simple google sheet :

In C2, i use simple query :

=query({A2:A15},"select Col1")

But all the non-numeric value has been skipped. Why is it ?

Answer 1

As MattKing already mentioned in the comments, query doesn't like mixed data type in a single column.

This is simply because, for query purposes, the majority data type determines the data type of the column. The minority data types are considered as null values.

There are still ways to circumvent the issue. Here are the possible alternatives:

1. Force the data to be treated as a single data type. (in this case, you can use to_text)

   =Arrayformula(query({to_text(A2:A15)},"select Col1"))

2. Using filter (MattKing)

   =filter(A2:A15, A2:A15 <> "")

3. Query with 1 as header (Broly)

   =query({A2:A15},"select Col1", 1)

4. Wrapping the range with arrayformula if you don't have any filtering to do.

   =arrayformula(A2:A15)

Output:

Reference:

• Query Mixed Data Type Issue

Answer 2

try:

=INDEX(QUERY({A2:A15}&"", "select Col1"))

---

UPDATE:

if QUERY is not able to evaluate mixed data then the only way how to tackle it is to convert input into plain text. this can be done in 5 ways:

appending trailing empty space:

=ARRAYFORMULA(QUERY(A:C&"", "select *", 0))

wrapping it into {}:

=ARRAYFORMULA(QUERY({A:C}, "select *", 0))

using formula:

=ARRAYFORMULA(QUERY(TO_TEXT(A:C), "select *", 0))

or partial combination. for example, if we know that column B is problematic but A and C are fine and shouldn't be converted we can do:

=ARRAYFORMULA(QUERY({A:A, TO_TEXT(B:B), C:C}, "select *", 0))

or formatting the source range as Plain Text

worth mentioning that either of these top 4 ways you use you will need ARRAYFORMULA wrapping and column references in SQL command needs to be Col1,Col2,Col3 instead of A,B,C

"yes, but I need outputted numeric values for further calculations"

nope, (in 99/100 cases) there is no need to actually have numeric values because by doing the calculation itself will convert automatically the plain text formatting into numeric values. example:

="123" + "2" 

if there is a special case when you totally need numeric values there is IFERROR workaround:

=ARRAYFORMULA(IFERROR(QUERY(TO_TEXT(A:C), "select *", 0)*1, 
                      QUERY(TO_TEXT(A:C), "select *", 0)))

by repeating the same thing twice this can get really long in some cases but it works. fortunately, this can be nicely refactored with LAMBDA like:

=ARRAYFORMULA(LAMBDA(x, IFERROR(x*1, x))
 (QUERY(TO_TEXT(A:C), "select *", 0)))