Excel Formula – Test if Cell Contains Specific Text

This Excel Tutorial covers how to test if a cell contains specific text.

To test if a cell contains specific text use the folowing formula:
=isnumber(search(find_text,within_text))
Test if Cell Contains Text

The SEARCH Function

The SEARCH Function looks for specific text (find_text) within a cell (within_text). If it finds the text, it returns the text’s numerical position in the cell. If it does not find the text, it returns an error (#VALUE!). In the above example:
=search(C5,B5)
Result: 1

It may be sufficient for this formula to output a number if text is found or an error if the text is not found. If so, just use the above formula. However, for a cleaner formula add the ISNUMBER Function.

The ISNUMBER Function

The ISNUMBER function tests if an expression results in a number. It returns TRUE for numbers and FALSE for anything else (including errors). In our example, ISNUMBER will return TRUE if the text is found within the cell and FALSE if not.
=ISNUMBER(SEARCH(C5,B5))
Result: TRUE

The SEARCH Function is not Case-Sensitive

The SEARCH Function is not case-sensitive. Searching for “Excel” will find “EXCEL”, “excel”, “Excel”, or any other combination of cases. For a case-sensitive text search use the FIND Function instead.

The FIND Function

The FIND Function is case-sensitive. If you search for “Excel”, “EXCEL” and “excel” will not produce matches.
Search For Text in a Cell
=ISNUMBER(FIND(C5,B5))

Return to Formula Examples

One Comment

  1. Özgür BircaN June 21, 2016 at 2:01 pm - Reply

    Time to test your Excel skills in Excel itself!
    http://www.someka.net/product/excel-formulas-trainer-excel-template/
    Well prepared questions, progress tracking, dashboard, error checking and many more.

Leave A Comment