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

.

CodePudding user response：

Try

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

or the contrary

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

Reference :

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.