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