# Query a column where numbers are mixed with blanks

I have a query where in a column there are numbers and blanks. It seems that the query function is treating everything in the column as string and not numbers. So when I sort, it doesn't sort numerically. How to fix this?

Right now my solution is to make another column and change everything in the desired column to values with =ArrayFormula(if(AN11:AN="",,value(AN11:AN))) then I sort by this new column. This works, but I don't want to make extra columns.

An arrayformula can be applied to data to be queried without creating a new column. Example: suppose I query columns A:C and want to apply an arrayformula to column B. This would look like this:

=query(arrayformula{A:A, arrayformula(if(B:B="", , value(B:B))), C:C},
"select Col1, Col2, Col3 order by Col2 desc")


here {} is array notation, where commas separate columns. The columns of this "virtual array" can only be referred to as Col1, Col2, etc - they do not have names like A, B, C because they are not really spreadsheet columns.

That said, I'm not sure your description of the problem is accurate. I could not reproduce it by putting many empty strings in a column with numbers. Putting in nonempty strings like " " does change the column type to numeric, coercing the numeric values to strings within query.

Looking at your formula if(AN11:AN="",,value(AN11:AN)) I suspect the values in column AN are already strings; otherwise you would not need "value" here.

In any case, the best solution is to fix the problem of data type at the root: do not put empty strings "" in spreadsheet cells (e.g., use if(A2>0, "foo", ) instead of if(A2>0, "foo", "")), do not store dates and numbers as strings, etc. The unexpected behaviour of query is merely a symptom of the incorrect data types in the spreadsheet.