Exceljet

Quick, clean, and to the point

Range contains one of many substrings

Excel formula: Range contains one of many substrings
Generic formula 
=SUMPRODUCT(COUNTIF(rng,"*"&substrings&"*"))>0
Explanation 

To test a range and determine if it contains one of many substrings (partial matches, specific text, etc.) you can use use a formula that uses the COUNTIF wrapped in the SUMPRODUCT function.

How the formula works

All the hard work is done by the COUNTIF function, which is configured to count the values in the named range "substrings" that appear the named range "rng" with like this:

COUNTIF(rng,"*"&substrings&"*"))

By wrapping substrings in the asterisks, Excel evaluates the formula like this:

=SUMPRODUCT(COUNTIF(rng,{"*dog*";"*green*";"*sun*";"*every*"}))>0

And COUNTIF counts the values where ever they appear in the cell. Since we are giving COUNTIF multiple values to look for, we receive a count for each value in an array like this: {1;0;1;1} .

Finally, SUMPRODUCT returns the sum of all items in the array. Any result greater than zero returns TRUE.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.