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:
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:
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.
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.
Return to Formula Examples