How can I use the google sheets OR function in conditional formatting with "text does not contain"?

user_14442548 0
Viewed 46

I have a range B6 to B26 that I'd like to format conditionally when a cell does not contain the words "Assessment","GOAL","or "Delete." This is what I have tried so far, unsuccessfully, in the "Text does not contain" option:

=OR("Assessment","GOAL","Delete")
=OR($B6="Assessment",$B6="GOAL",$B6="Delete")
=OR(B:B="Assessment",B:B="GOAL",B:B="Delete") <throws an error>

I've tried a few other things that were clearly shots in the dark that I'll not list. What's the solution?

Thanks

.

conditional-formatting google-sheets
2 Answers

Try

=REGEXMATCH(B6,"Delete|GOAL|Assessment")

or the contrary

=NOT(REGEXMATCH(B6,"Delete|GOAL|Assessment"))

enter image description here

Reference : REGEXMATCH

user_150193800

Basically just use three different, text does not contain conditional formatting rules for a range, one searching for GOAL, another for Assessment, and lastly looking for Delete... But you could also use custom function instead!

Example of Conditional Formatting using custom function

Formula:

=(OR(IFERROR(FIND("assessment",LOWER(A2)),-1) > -1, IFERROR(FIND("goal",LOWER(A2)),-1) > -1, IFERROR(FIND("delete",LOWER(A2)),-1) > -1))

Replace the A2s in my formula with whatever your top leftmost comparison of your array you are comparing should be. See the A2 in A2:A1000 in my Apply to Range box?

I'll explain how it works in a second, but I highly recommend you look into data validation. It can help clear up some confusion if the sheet is shared among several users.

OK so first in my or statement I give an

IFERROR( something..., value if something throws an error).

This is because I expect the FIND function to give me an error if it can't find the value I'm giving it. FIND(needle, haystack). If it does find the text, it will return the position of the value (or needle) I gave it to find in the text (haystack). And if it doesn't it will return to my IFERROR an error and IFERROR will return a negative number.

Great, but what if I can't find my needle in the haystack because of capitalization discrepancy's ("Needle", "NEEDLE", "NeEdLe")? I'll just make the entire haystack lowercase, and look for a lowercase "needle". We do this by using the LOWER formula

And lastly now we just need to make sure that It is larger than -1, and then we're done... with one keyword. We duplicate the function two more times for the additional keywords and wrap it in an OR.

user_156275900
Related