Query order by ASC in google spreadsheets

sorting google-sheets google-query-language
Mary picture Mary · Feb 1, 2018 · Viewed 7.9k times · Source

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?

Answer

Tom Sharpe picture Tom Sharpe · Feb 1, 2018

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).

Related questions

How to sort only those rows which have no blank cell?

I have a Google Spreadsheet with two separate sheets. The first one is just a big list of names and data, and the second is supposed to be a sorted listing of all the data on the first sheet (sorted …

Read More
Want to auto sort a Google sheets spreadsheet on edit, but want to sort by date

Been looking around for a solution to this for a while and not finding anything that works, unfortunately. I have tried copying and pasting several bits of code but cannot get my sales pipeline sheet to automatically sort on edit …

Read More
How do I sort a dictionary by value?

I have a dictionary of values read from two fields in a database: a string field and a numeric field. The string field is unique, so that is the key of the dictionary. I can sort on the keys, but …

Read More