-1

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.