Custom sort with regular expressions?

user_1202124 0
Viewed 523

I have two columns, first with a list of names and second with their rating. I want to use custom sort, for which I use the following (found it here) :

=sort(A33:B50;match(B33:B50;{"Great";"Good";"OK";"Bad");true)

Which works, but my ratings are actually:

  • Great+
  • Great
  • Great-
  • Good+
  • Good
  • Good-
  • OK+
  • ...

Is there any way where I can combine the formula above with regular expressions? Something along the lines of this:

=sort(A33:B50;match(B33:B50;{"Great*";"Good*";"OK*";"Bad*");true)

Which doesn't really do anything. Checked out the regex formulas of Google sheets, but couldn't find any that would do the trick in this situation.

Cheers!

PS: A workaround would be 

=sort(A33:B50;match(B33:B50;{"Great+";"Great";"Great-";"Good+";"Good";"Good-";"OK+";"OK";"OK-";"Bad+";"Bad";"Bad-");true)

but I'm curious if there's a less tedious way of doing this

re2 google-sheets sorting regex
1 Answers

=sort(A1:B7;match(regexextract(B1:B7;"Great|Good|OK|Bad");{"Great";"Good";"OK";"Bad"};0);true)

  • Pipeline | is for OR login in Regex.
  • Change A1:B7 and B1:B7 to your ranges.

Edit

  • for sorting Good+ Good Good- change regex to "Great|Good\+|Good\-|Good|OK|Bad", change the array to {"Great";"Good+";"Good";"Good-";"OK";"Bad"}

counter-intuitive: the order in the regextract is Good+|Good-|Good and in the array {"Great";"Good+";"Good";"Good-";"OK";"Bomb"} (Good in the regex was already capturing Good- instances)

user_53724000
Related