when using the Query function to sort the numbers are out of order. otherwise, the formula is working fine.

It will basically sort as follows; 1 11 12 13 15 15 16 17 18 19 2 20 21 3 4 5 6 7 8 9

Is there a way to get it to sort correctly in numerical order?

Thank you!

CodePudding user response：

First of all, make sure that the format of your data is numerical and not in plain text. You can set the format by highlighting the range of data and apply numerical formatting by using Format > Number > Automatic.

On the other hand, you can use a combination of ARRAYFORMULA within a query formula: =query({A1:A21,ARRAYFORMULA(A1:A21*1)}, "select Col1 order by Col2")

CodePudding user response：

your input is not numerical. you need to convert it and then it will sorts correctly. now (as you shown in your example) it sorts in alphabetical order

to convert it you can for example multiply your column by 1