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

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.

=ISNUMBER(FIND(C5,B5))

Return to Formula Examples