IF Cell contains specific text

cell-contains-specific-text

Today’s tutorial will show you how to find if a cell contains specific text using the ISNUMBER and SEARCH functions.

If you are working with string manipulation functions in Excel, it can be useful if you want to identify a substring in a text string. This guide will show three methods to perform the task. You can use built-in Excel functions and conditional formatting.

How to find if a cell contains a specific text in Excel?

Generic formula to find if a cell contains a specific part of the arbitrary text:

=ISNUMBER(SEARCH(substring, text))

Arguments:

  • substring = the text that 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, the formula in F3 is the following:

=ISNUMBER(SEARCH(D3,B3))

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

Note: Don’t forget that you’ll get partial matches using the SEARCH function.

How to use the “if cell contains text” formula

First, the SEARCH function locates the position of the search string when found. If the cell does not contain specific text, the result is #VALUE! Error.

how-the-formula-works

The ISNUMBER function helps us to get the proper numeric position. Let’s see the two 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 and 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 FALSE.

Case-sensitive formula using the FIND function

Using a simple IF function, the result is TRUE or FALSE.

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

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

=IF(ISNUMBER(SEARCH(substring,text)), “Found”, “Not found”)
=IF(ISNUMBER(SEARCH(D3,B3)), “Found”, “Not found”)

Case-sensitive-formula-using-the-FIND-function

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

How to highlight a row that contains specific text

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

Steps to highlighting the matching cells:

  1. Select the range that 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
format-cells-that-contain-using-conditional-formatting

The result is the same as the examples mentioned above.