These strings can appearanywherein the cell, so this is a literal “contains” problem.
If the cell contains the substring, the formula returns TRUE.
If SEARCHdoesn’tfind a string, it returns a #VALUE!
This means ISNUMBER will return TRUE if there is a match and FALSE if not.
“green” begins at the 23rd character).
The easiest way to check for TRUE is to add all values together.
you might also use anarray constant.
This makes the formula “more friendly” to most users.
since “dr” appears inside these words.
This happens because SEARCH automatically does a “contains” match.
For a quick hack, it’s possible for you to add space around the search words (i.e. "
dr “, or “dr “) to avoid catching “dr” in another word.
Then you use the formula on this page on the resulting text.
SEARCH Function
The Excel SEARCH function returns the location of one text string inside another.
SEARCH returns the position offind_textinsidewithin_textas a number.
SEARCH supports wildcards, and isnotcase-sensitive….
