Summary

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 E5 is:

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

Generic formula

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

Explanation 

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")

Related formulas

Excel formula: Value exists in a range

Value exists in a range

In this example, the goal is to use a formula to check if a specific value exists in a range. The easiest way to do this is to use the COUNTIF function to count occurences of a value in a range, then use the count to create a final result. COUNTIF function The COUNTIF function counts cells that...
Excel formula: Range contains one of many values

Range contains one of many values

Each item in rng is compared to each item in values and the result is an array of TRUE or FALSE values. The double negative will force the TRUE and FALSE values to 1 and 0 respectively. Since SUMPRODUCT receives just one array, it simply adds up the items in the array and returns the result...
Excel formula: Range contains one of many substrings

Range contains one of many substrings

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,"*"...
Excel formula: Cell contains specific text

Cell contains specific text

In this example, the goal is to test a value in a cell to see if it contains a specific substring . Excel contains two functions designed to check the occurrence of one text string inside another: the SEARCH function and the FIND function. Both functions return the position of the substring if...
Excel formula: Cell contains one of many things

Cell contains one of many things

The goal of this example is to test each cell in B5:B14 to see if it contains any of the strings in the named range things (E5:E7). These strings can appear anywhere in the cell, so this is a literal "contains" problem. The formula in C5, copied down, is: =SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))...
Excel formula: Range contains specific date

Range contains specific date

First, it's important to note first that Excel dates are simply large serial numbers . When we check for a date with a formula, we are looking for a specific large number, not text . This formula is a basic example of using the COUNTIFS function with just one condition. The named range dates is...

Related functions

Excel COUNTIF function

COUNTIF Function

The Excel COUNTIF function returns the count of cells in a range that meet a single condition. The generic syntax is COUNTIF(range, criteria), where "range" contains the cells to count, and "criteria" is a condition that must be true for a cell to be counted. COUNTIF can be used to count cells...
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Functions

Training