CODEWITHSUNDEEP
regexgoogle-sheets
Jump_Ace
Jump_Ace

Reputation: 303

Regex Formula & Google Sheets

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!

Upvotes: 0

Views: 67

Answers (3)

Jump_Ace
Jump_Ace

Reputation: 303

Here's what is working for me now.

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

Upvotes: 0

player0
player0

Reputation: 1

try:

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

enter image description here

fx:

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

Upvotes: 0

The fourth bird
The fourth bird

Reputation: 163632

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 $

Upvotes: 1

Related Questions