Cell contains specific text

In the example we’ll show you how to check if cell contains specific text.

Formula to check if a cell contains specific text

=ISNUMBER(SEARCH(substring,text))

Let us see how to check if a cell contains specific text use a simple combination of regular excel functions. Use nested functions in a formula to combine the ISNUMBER and SEARCH functions.

Arguments of the formula:

  • substring = the text what we find
  • text = cell reference

We want to find a substring in a cell or string; the text is the variable we are looking for.

In the example demonstrated, the formula in B2 is:

=ISNUMBER(SEARCH(C2,B2))

The formula has multiple (in this case, two) outputs: If the substring exists, we’ll get TRUE as a result. If the substring is not available, the result is FALSE.

cell contains specific text formula example

Don’t forget: you’ll get partial matches too if you are using the SEARCH function.

How to use the formula

As first, the SEARCH function locates the position of search string when found. IF the cell does not contain specific text, the result is #VALUE! error.

The ISNUMBER function helps us to get the proper numeric position.

Let’s see the possible cases!

ISNUMBER returns:

  • TRUE for numbers
  • FALSE for other cell types

If the cell contains specific text, the SEARCH function locates the given substring, then returns the proper position as a value (number). In this case, the ISNUMBER gets a TRUE result.

Otherwise – if the cell does not contain specific text – SEARCH returns a #VALUE! error, therefore the ISNUMBER returns with FALSE.

Case sensitive formula using the FIND function

A simple IF statement gets a result, TRUE or FALSE.

=IF(ISNUMBER(SEARCH(substring,text)))

To create a more user-friendly output when a cell contains specific text, add a third function to the formula.

case sensitive formula example
=IF(ISNUMBER(SEARCH(substring,text)), "Contains", "Not contains")

Instead of returning TRUE or FALSE, the formula above will return a more friendly output. The result is “Contains” if a substring is found elsewhere “Not contains“.

Highlight a row that contains specific text

If you want to highlight cells in a range that contain specific text, the fastest way to use conditional formatting.

Only a few simple steps necessary:

  1. Select the range which contains text
  2. Go to the Home tab and click Conditional formatting
  3. Choose “Highlight Cells Rules” > “Text that contains….” Option
  4. Choose your preferred formatting style and click OK
apply formatting if cell contains specific text

The result is the same as the above-mentioned examples.

enter the text

Additional resources

How clean data in Excel using string manipulation functions?