I am aware that regex does not work with numeric values, yet, how can we have a regex pattern to differentiate between numbers in a Google sheets formula?

For example: >=13 and <13

I can solve this in alternative ways like:

=INDEX(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE("" & A2:A16,
 "\b(^" & JOIN("$|^", SEQUENCE(13, 1, )) & "$)\b", "$1♥"),
 "(^-\d )", "$1♥"),
 "(\d $)", "$1♦♦♦"))

...but I feel there could be some more "regexy" solution.

Rules of combat:

• regex pattern
• no IF-statements
• >=13 add ♦♦♦
• <13 add ♥
• no IFERROR pivots
• no non-regex solutions

CodePudding user response：

You could first add the diamonds and then replace those by a heart when the number is less than 13:

=REGEXREPLACE(A1 & "♦♦♦"; "^(-\d |1[0-2]|\d)♦♦♦$"; "$1♥")

The regular expression looks for three patterns:

• -\d : any negative number
• 1[0-2]: 10, 11 or 12
• \d: 0,1,2,..., or 9.

CodePudding user response：

Try:

=REGEXREPLACE(REGEXREPLACE(TO_TEXT(A1), "^(-\d |[0-9]|1[0-2])$", "$1♥"), "^([^♥] )$", "$1♦♦♦")