CODEWITHSUNDEEP
google-sheetsgs-conditional-formatting
ecstrema
ecstrema

Reputation: 691

Google spreadsheets conditional formatting if text contains numbers

I have a sheet containing my weekly schedule. Only school cells have a room number in it, so how do I format the cells to color only the ones that contain a number.

note: Actually, the room number is a number in range(A:E) followed by a three digits number in range(000:499). Ex.:(A433, B166, D254)

I tried: Text contains"(A:F)(000:444)" but it didn't work.

EDIT: For some reason, "=REGEXMATCH(B2, "[A-F][0-9]{3}")" worked. Could anyone tell me why? I tried replacing B2 by B1, but then it didn't work. Does it have anything to do with the fact that B1 is a weekday, and so does not contain REGEXP(B1,"[A-F][0-9]{3}) returned false.

What seemed more logical to me was "=REGEXMATCH(B2:F22, "[A-F][0-9]{3}")" To apply this function in range B2 to F22. What am I missing here?

Upvotes: 0

Views: 8066

Answers (2)

pnuts
pnuts

Reputation: 59442

You might try Conditional Formatting with a custom formula rule of the type:

=if(isnumber(A1),1,regexmatch(A1,"\d"))

The above was an attempt to respond to:

Google spreadsheets conditional formatting if text contains numbers

A more particular fit for the stated room number style would be:

=REGEXMATCH(A1,"[A-F]\d\d\d")

where the first character is any of the first six letters of the alphabet, if capitalised, followed by three instances of any number.

Upvotes: 1

Eric Barr
Eric Barr

Reputation: 4155

In order to match patterns, you'll need to use regular expressions. Since the standard Conditional Formatting options don't include regular expressions, you'll need to choose "Custom formula is" and then use REGEXMATCH, which returns a Boolean value.

If you really want to look for the specific room number format you mentioned, then you would use the formula:

=REGEXMATCH(A1, "[A-E][0-9]{3}")

But if you just want to look for any numbers, you can use 

=REGEXMATCH(A1, "[0-9]+")

In both cases, the text you're checking is in cell A1

Upvotes: 2

Related Questions