In the example we’ll show you how to check if cell contains specific text.
Formula to check if a cell contains specific text
Let us see how to check if a cell contains specific text use a simple combination of regular excel functions. Use nested functions in a formula to combine the ISNUMBER and SEARCH functions.
Arguments of the formula:
- substring = the text what we find
- text = cell reference
We want to find a substring in a cell or string; the text is the variable we are looking for.
In the example demonstrated, the formula in B2 is:
The formula has multiple (in this case, two) outputs: If the substring exists, we’ll get TRUE as a result. If the substring is not available, the result is FALSE.
Don’t forget: you’ll get partial matches too if you are using the SEARCH function.
How to use the formula
As first, the SEARCH function locates the position of search string when found. IF the cell does not contain specific text, the result is #VALUE! error.
The ISNUMBER function helps us to get the proper numeric position.
Let’s see the possible cases!
- TRUE for numbers
- FALSE for other cell types
If the cell contains specific text, the SEARCH function locates the given substring, then returns the proper position as a value (number). In this case, the ISNUMBER gets a TRUE result.
Otherwise – if the cell does not contain specific text – SEARCH returns a #VALUE! error, therefore the ISNUMBER returns with FALSE.
Case sensitive formula using the FIND function
A simple IF statement gets a result, TRUE or FALSE.
To create a more user-friendly output when a cell contains specific text, add a third function to the formula.
=IF(ISNUMBER(SEARCH(substring,text)), "Contains", "Not contains")
Instead of returning TRUE or FALSE, the formula above will return a more friendly output. The result is “Contains” if a substring is found elsewhere “Not contains“.
Highlight a row that contains specific text
If you want to highlight cells in a range that contain specific text, the fastest way to use conditional formatting.
Only a few simple steps necessary:
- Select the range which contains text
- Go to the Home tab and click Conditional formatting
- Choose “Highlight Cells Rules” > “Text that contains….” Option
- Choose your preferred formatting style and click OK
The result is the same as the above-mentioned examples.