Explanation
The goal is to do something if a cell contains one substring or another.
Most users will think first of the IF function.
However, one problem with IF is that it doesnot support wildcardslike “?”
and “*”.
Another approach is to use the COUNTIF function with the SUM function to create the logical test.
Both approaches are explained below.
If SEARCH finds the substring, it returns thepositionof the substring in the text as a number.
If the substring is not found, SEARCH returns a #VALUE error.
For example:
TheISNUMBER function.
If SEARCHdoesn’tfind the substring, it returns an error, and ISNUMBER returns FALSE.
It works like a range in Excel, except the values in an array constant are hard coded.
Because we are giving SEARCH two substrings, it will return two results.
Note: the SEARCH function is not case-sensitive.
If you need a case-sensitive option it’s possible for you to switch to theFIND functionasexplained here.
You are free to adjust the IF formula to return whatever values you like.
Note: the IF function simply leaves an “x” in a cell as a marker.
If the goal is to retrieve all matching cells or records, see theFILTER function.
As a result, COUNTIF will return anarrayof counts, one count per condition.
The result goes into the IF function as thelogical_test.
Any non-zero number will be evaluated as TRUE.
This is what allows COUNTIF to count the substrings anywhere in the text (i.e.
this provides the “contains” behavior).
SEARCH returns the position offind_textinsidewithin_textas a number.
OR returns TRUEif any condition is TRUE.
If all conditions are FALSE, the OR function returns FALSE.
These values can be numbers, cell references, ranges, arrays, and constants, in any combination.
SUM can handle up to 255 individual arguments.
Related videos
The IF function
If this OR that
