Regex Formula & Google Sheets-CodePudding

I'm working on a google sheet and I need to associate some equipment with a room number. I'm looking for a pattern in a cell, like this: CS1-4. The CS will always be before a single number (1-4), followed by a '-' and then followed by 1 or 2 numbers. So I may have CS1-5 or CS1-12 for example. Anytime I have CS1-9, I get a #REF error in google sheets.

Here's the code I have now:

=IF(REGEXMATCH(D10, "CS([1-4]-[1-4])"), 220, IF(REGEXMATCH(D10, "CS([1-4]-[5-8])"), 240, IF(REGEXMATCH(D10, "CS([1-4]-[9-12])"), 260, IF(REGEXMATCH(D10, "CS([1-4]-[13-16])"), 280, ""))))

It seems like it's only looking at the first number after the dash instead of looking to see if there's two digits after the dash. Any help would be appreciated, thanks!

CodePudding user response：

try:

"CS[1-4]-9|10|11|12"

fx:

=IF(REGEXMATCH(D10, "CS[1-4]-[1-4]"),       220, 
 IF(REGEXMATCH(D10, "CS[1-4]-[5-8]"),       240, 
 IF(REGEXMATCH(D10, "CS[1-4]-9|10|11|12"),  260, 
 IF(REGEXMATCH(D10, "CS[1-4]-13|14|15|16"), 280, ))))

CodePudding user response：

This notation [9-12] is not a range from 9-12, it is an invalid range.

The pattern with a number 1-4 and 9-12 can be written as:

CS-[1-4]-(9|1[012])

And 13-16

CS-[1-4]-1[3-6]

If you don't want partial matches, you can add word boundaries \b to the left and right of the pattern, or us anchors ^ and $