How to check if a cell contains specific text
✓ Verified in LibreOffice 25.8.7.3Return Yes/No (or anything else) depending on whether a cell contains a word or substring.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =IF(ISNUMBER(SEARCH("red",A2)),"Yes","No") | SEARCH is case-insensitive; use FIND for case-sensitive matching. |
| Google Sheets | =IF(ISNUMBER(SEARCH("red",A2)),"Yes","No") | Identical; REGEXMATCH(A2,"red") is a Sheets-only shortcut. |
| LibreOffice Calc | =IF(ISNUMBER(SEARCH("red",A2)),"Yes","No") | Identical. |
How it works
SEARCH returns the position of "red" inside the text (here 6) or a #VALUE! error when it's absent. ISNUMBER turns that into TRUE/FALSE, and IF maps it to your labels. You can't write =IF(A2="*red*",...) — wildcards don't work in a plain equals test, which is the usual reason this formula surprises people. To count or sum by contains instead, COUNTIF/SUMIF do accept wildcards: =COUNTIF(A:A,"*red*").
Verified, not just documented
We ran =IF(ISNUMBER(SEARCH("red",A2)),"Yes","No") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned Yes — exactly the expected result. Every formula here is confirmed by actually executing it.