1
I'd like to create a filter using a custom formula that will display only cells that contain the word "Red" or "Blue" (as an example) as part of a longer string. These words are mutually exclusive so the two will never appear in the same cell.
For exact matches I believe the custom formula would be something like this
=OR(A1:A1000 = "Red",A1:A1000 = "Blue")
But this only returns cells with an exact match on those words in column A. I'm not sure what I can do to replace the = with a "contains" operator to achieve what I'm looking for.
dekaliber
Posted 2019-02-27T22:25:59.380
Reputation: 111
2It might be easier to use a helper column with a formula like
=IF(OR(SEARCH("Red",A1)>0,SEARCH("Blue",A1)>0),"Found","Not Found")and then filter on that – cybernetic.nomad – 2019-02-27T22:36:32.8331It isn't clear how you want to apply it, but you may be able to do exact matches with wildcards (
"*Red*"and"*Blue*"). The problem you may run into with any kind of "contains" function is examples that aren't well-behaved. For example, a word like "redline". You can get around that by including spaces around the word inside the wildcard or search expression. But then that doesn't work at the beginning or end of the phrase, or if it is delimited with punctuation instead of a space. Depending on what you need to deal with in your data, this can get complicated. – fixer1234 – 2019-02-28T09:11:04.830