4

I'm indexing a document with sections and sub-sections. I want to have one column representing the section-number and be able to sort by this column. However when the sub-section gets higher than 10, the sort no longer works.

This is the order I want...

1-1
1-2
1-10
2-1

Of course when we know this represents sections and sub-sections (or for example major and minor version numbers) that 1-10 is higher than 1-2, but of course both alphabetical and numerical sorting produces this order:

1-1
1-10
1-2
2-1

Is there a way to use numeric string sort (like -V to sort) in Google Sheets?

1

The following sorts a range (like A1:A7) of such strings:

=array_constrain(sort({A1:A7, value(regexextract(A1:A7, "^\d+")), value(regexextract(A1:A7, "\d+$"))}, 2, true, 3, true), 10000000, 1)

Same formula with linebreaks for readability:

=array_constrain(
   sort(
     {
       A1:A7, 
       value(regexextract(A1:A7, "^\d+")), 
       value(regexextract(A1:A7, "\d+$"))
     }, 
   2, true, 3, true), 
 10000000, 1)

Given n rows, this creates a rectangular array with three columns, where the 2nd and 3rd columns are numbers extracted from the string. The sort command sorts first by 2nd, then by 3rd. Finally, array_constrain outputs only the first column of the sorted array.

0

What could be useful is instead of using

1-1
1-2
...
1-10

you can use as much "digits" as your largest number; like this:

1-01
1-02
...
1-99

In this case my largest number will be 99.

  • And a month later, you need 100 and 101. Then what? – user79865 Mar 23 '16 at 15:43
  • So far, I've been trying several options and still don`t succeed. So, until now, the best solution I can to figure it out is to add one more digit. i.e. 1-001 – Enrique Ramírez Mar 23 '16 at 17:50

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.