Exceljet

Quick, clean, and to the point

Range contains specific text

Excel formula: Range contains specific text
Generic formula 
=COUNTIF(rng,"*"&value&"*")>0
Explanation 

To determine if a range or column contains specific text (a specific substring or partial text), you can use a formula based on the COUNTIF function and wildcards. In the example shown, the formula in D5 is:

=COUNTIF(rng,"*"&D5&"*")>0

How this formula works

The COUNTIF function counts cells that meet supplied criteria, and returns a count of occurrences found. If no cells meet criteria, COUNTIF returns zero.

The asterisk (*) is a wildcard for one or more characters. By concatenating asterisks before and after the value in D5, the formula will count the value as a substring. In other words, it will count the value if it appears anywhere inside any cell in the range.

Any positive result means the value was found. By comparing the result with the greater than operator (>) and zero, we force a final result of TRUE or FALSE.

With IF

You can nest this formula inside the IF function as the logical test. For example, to return  a final result of "Yes" or "No",  you can use IF like this:

=IF(COUNTIF(range,"*"&value&"*"),"Yes","No")
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.