I have a Google sheet for storing cross references and I want to automatically sort those cross references in ascending order based on the New Testament verses.

I'd to be able to sort by the NT Verse column in ascending order. However, due to the Scripture references, you can see that reference 1:12 is smaller than 1:2 when it should be larger.

I also am trying to do this with a =SORT Function on a query:

=SORT(QUERY({'1 Chronicles'!A:O,Ezra!A:O}, 'select Col4, Col5, Col1, Col2, Col3, where Col4 contains 'Matthew' order by Col3 ASC",0),3,true)

So in other words, I need to sort by the NT Verse column in ascending order in a way that retains the order of the verse numbers on the right side of the colon.

I'm not sure if there is a way to do this by formatting the column or with the =SORT function?

CodePudding user response：

This can be done using the SPLIT function, keeping everything contained in the query. Use ARRAYFORMULA(SPLIT(X:X,":")) as part of the query's first argument (where X is your NT Verse column), then add order by Col6, Col7 to the end of the query's second argument. The SORT function is not necessary.

Here's an example, although I could not test it as you have not provided a full sample data set.

=QUERY({{'1 Chronicles'!A:E;Ezra!A:E},ARRAYFORMULA(SPLIT({'1 Chronicles'!C:C;Ezra!C:C},":"))}, "select Col4, Col5, Col1, Col2, Col3, where Col4 contains 'Matthew' order by Col6, Col7",0)

My locale uses semicolons to separate an array by column, whereas it seems yours uses commas. Make sure to adjust appropriately. Also note that I changed the single quote that begins your query's second argument to a double quote, assuming it was a typo.

CodePudding user response：

try:

=SORT(A2:E, INDEX(SPLIT(C2:C, ":"),,1), 1, INDEX(SPLIT(C2:C, ":"),,2), 1)