1

I am trying to sort a table of rows by their first column that is comprised of strings with this format (capital letters followed by a dash followed by numbers).

Using the default string sort I get something like this:

BB-1
BB-11
BB-12
BB-2
BB-3
BL-1
BL-14
BL-15
MAN-4
MAN-45
MAN-5

How can I sort it first by the char prefix followed by the integer suffix so my result is as follows:

BB-1
BB-2
BB-3
BB-11
BB-12
BL-1
BL-14
BL-15
MAN-4
MAN-5
MAN-45

I've attempted to do this using a formula with some regular expressions, but it doesn't seem to do the trick:

=sort(A2:A,REGEXEXTRACT(A2:A,"^\w+"),true, REGEXEXTRACT(A2:A, "\d+$"), true)
1

This formula did the trick:

=sort(A2:A,REGEXEXTRACT(A2:A,"^\w+"),true, VALUE(REGEXEXTRACT(A2:A, "\d+$")), true)

My first attempt was missing the string to value conversion.

| improve this answer | |
  • Nice solution. Thank you. – marikamitsos Aug 14 at 0:14

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.