2

I have a google spreadsheet that uses the following query:

=query('Respostas do Formulário 1'!A1:AG;"select D,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,AF WHERE B='7ºA' order by D ASC";1)

However column D from sheet: "Respostas do Formulário 1", has values with a number then a name in front and when it performs the query in sheet1 I wanted it to order ASC, like 1, 2,3,.., 10, 11,12.. But as you see in the image that is not happening...

enter image description here

Is there anyway I can accomplish this?

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Mary
  • 301
  • 1
  • 2
  • 10

3 Answers3

3

If you always have a hyphen (-) after the number, you could try extracting the number and sorting on it before doing the query:

=query(sort(A:A,value(left(A:A,(search("-",A:A)-1))),true),"Select  Col1 where Col1<'999'")

(change ranges to suit).

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
3

You need to convert strings to numbers:

  • 1 - xxxxx to 1
  • 10 - yyyy to 10
  • ...

Strings are sorted as a string: "1", "10", "11", ..., "2", ..., "22", "23", ...

Means they are sorted by the first char, then the second and so on.

Solution #1 is to make an extra column in your data sheet and extract numbers with a formula like REGEXEXTRACT(A2,"^\d+")*1.

Solution #2 is to get numbers on the fly. Heres sample formula:

=QUERY(FILTER({REGEXEXTRACT(A:A,"^\d+")*1,A:C},B:B="a"),"select * order by Col1")

enter image description here

Notes:

  • The formula uses the filter to combine the array. Filter limits the range to only rows needed.
  • {} in a filter is used to combine the array.
  • REGEXEXTRACT(A:A,"^\d+")*1 will give the column of numbers. ^ means the beginning of a string, \d means digit, + means one or more. *1 is to convert the string "10" into the number 10
  • The query uses Col1 notation because the source is an array.
Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
0

If anyone else is having the problem of QUERY function sorting lexicographically a numerical column, I solved it by selecting an apropiate format in the numerical column so the numerical order coincides with the lexicographical order.

Example

Suppose the column B has 2 digit integers (i.e. numbers form 0 to 99)

By adding the personalized number format "0#" in the column B, the numbers are displayed as 01, 02, 03, ..., 99. So the formula

=query(A1:B100,"SELECT * ORDER BY B ASC")

Works as one would like.

This requieres knowing the numbers of digits in advance. For numbers from 0 to 1000, the format would be "00#"

Santiago Armstrong
  • 51
  • 7