Explanation
The goal is to do something if a cell contains a given substring.
If you are familiar with Excel, you will probably think first of the IF function.
However, one limitation of IF is that it doesnot support wildcardslike “?”
and “*”.
This approach is explained below.
If SEARCH finds the text, it returns thepositionof the text as a number.
If the text is not found, SEARCH returns a #VALUE error.
error for “z”, which is not found.
The screen below shows how the formulas above can be transferred to a workbook.
error as in the last example, where the word “dog” is not found.
The screen below shows how the formulas can be set up in a spreadsheet.
The text to search is in column B.
The word to search for is typed directly into the SEARCH function:
The formulas above work fine.
Enter the ISNUMBER function.
The SEARCH function doesn’t work by itself because it returns either a numeric position or an error.
If SEARCHcan’tfind the text, it returns an error, and ISNUMBER returns FALSE.
We now have what we need to create an IF statement to find out if a cell contains text.
If “abc” is found anywhere in cell B5, SEARCH returns a number and ISNUMBER returns TRUE.
The IF function then returns “x” as a final result.
If “abc” isnot found, SEARCH returns an error and ISNUMBER returns FALSE.
The IF function then returns an empty string ("") as a final result.
If FALSE, we supply an empty string ("") which will display as a blank cell.
If the count is a positive number, the IF function will evaluate the number as TRUE.
More than one condition can be tested by nesting IF functions.
SEARCH returns the position offind_textinsidewithin_textas a number.
Related videos
The IF function
