you’re free to also use SEARCH to look to see if a cell contains specific text.
SEARCH isnot case-sensitive, which means it does not distinguish between uppercase and lowercase letters.
In addition, SEARCH supports the use ofwildcardslike *?~, allowing more flexible search patterns.
Both functions return the position of one text string inside another.
However, unlike FIND, SEARCH isnotcase-sensitive anddoessupport wildcards.
When SEARCH locates the substring, it returns the position of the substring in the text as a number.
If the substring is not found, SEARCH returns a #VALUE error.
To force a TRUE or FALSE result, you cannestthe SEARCH function inside theISNUMBER function.
ISNUMBER returns TRUE for numeric values and FALSE for anything else.
This approach allows for support for wildcards in the search.
For a more detailed explanation of this approach, with many more examples,see this example.
We then add 1, and the result is used as thestart_numin theouterSEARCH.
The result is the location of the second “x” in cell A1.
This allows for more complex searches, including basic pattern matching.
For example, the question mark (?)
wildcard matches any one character.
), and tilde (~) in that order.
More advanced formulas
The SEARCH function shows up in many more advanced formulas that work with text.
When the text is not found, FIND returns a #VALUE error.
For example =REPLACE(“XYZ123”,4,3,“456”) returns “XYZ456”.
SUBSTITUTE Function
The Excel SUBSTITUTE function replaces text in a given string by matching.
SUBSTITUTE is case-sensitive and does not support wildcards….
