Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to return a value if a cell contains text in Excel and Google Sheets
Topic:IF, IFS & nested conditionsExcel & Google Sheets
=IF(ISNUMBER(SEARCH("apple",A2)),"Contains apple","Does not contain")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Product | Flag |
| apple juice | |
| orange soda | |
| apple cider | |
| mango drink |
=IF(ISNUMBER(SEARCH("apple",A2)),"Contains apple","Does not contain")→Contains apple
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Start with SEARCH to locate the text: SEARCH("apple",A2) returns the position number when "apple" appears anywhere in A2, and a #VALUE! error when it does not. SEARCH is case-insensitive.
- 2Wrap it in ISNUMBER to convert that to TRUE/FALSE: ISNUMBER(SEARCH("apple",A2)) is TRUE when the text is present.
- 3Wrap in IF to return your values: =IF(ISNUMBER(SEARCH("apple",A2)),"Contains apple","Does not contain").
- 4To return something other than fixed text, put a cell reference or formula in the IF branches: =IF(ISNUMBER(SEARCH("apple",A2)),B2,"") returns the matching row’s price instead of a label.
- 5For a case-sensitive check, swap SEARCH for FIND: =IF(ISNUMBER(FIND("Apple",A2)),"Match","No match") — FIND("Apple",…) will not match "apple".
- 6Copy the formula down the column; each row evaluates independently.
Tips
- Check for ANY of several words with OR: =IF(OR(ISNUMBER(SEARCH("apple",A2)),ISNUMBER(SEARCH("mango",A2))),"Fruit","Other").
- Check that ALL keywords appear with AND: =IF(AND(ISNUMBER(SEARCH("red",A2)),ISNUMBER(SEARCH("apple",A2))),"Red apple","No").
- When you only need to count or sum matching rows, skip the helper column: =COUNTIF(A:A,"*apple*") and =SUMIF(A:A,"*apple*",B:B) accept wildcards directly.
- The search text can live in a cell: =IF(ISNUMBER(SEARCH($D$1,A2)),"Match","") lets you change the keyword once instead of editing every formula.
- SEARCH also supports wildcards itself: SEARCH("a?ple",A2) matches "apple" and "ample" (? = one character, * = any run of characters).
- To test whether the cell contains any text at all rather than a specific word, use =IF(ISTEXT(A2),"Is text","Not text").
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Why use ISNUMBER instead of just SEARCH?
SEARCH returns a #VALUE! error when the text is not found, not FALSE — so IF cannot use it directly. ISNUMBER maps "found" (a number) to TRUE and "not found" (an error) to FALSE.
How do I make the check case-sensitive?
Use FIND instead of SEARCH: =IF(ISNUMBER(FIND("Apple",A2)),"Match","No match"). FIND distinguishes upper and lower case; SEARCH does not.
How do I check for multiple keywords at once?
Wrap several ISNUMBER(SEARCH(…)) tests in OR (any keyword) or AND (all keywords): =IF(OR(ISNUMBER(SEARCH("apple",A2)),ISNUMBER(SEARCH("pear",A2))),"Fruit","Other").
Can I use wildcards instead of SEARCH?
Inside COUNTIF/SUMIF/SUMIFS, yes: "*apple*" matches the word anywhere in the cell. IF itself does not accept wildcards in a plain comparison, which is why the ISNUMBER(SEARCH(…)) pattern exists.
How do I return a value from another column when the text matches?
Point the IF branches at cells instead of strings: =IF(ISNUMBER(SEARCH("apple",A2)),B2,""). The row’s value in column B is returned only for matching rows.
More on IF, IFS & nested conditions
See all →How-to
How to hide #N/A and other formula errors
Replace #N/A, #DIV/0!, and other errors with a blank or a friendly message using IFERROR or IFNA. Works in Excel and Google Sheets.
Error fixFix Nested IF
Nested IF formulas not working in Excel and Google Sheets — fix bracket mismatches, wrong evaluation order, overlapping conditions, and the 64-nesting limit.
How-toHow to return blank instead of zero
Stop formulas showing 0: wrap them in IF to return an empty string, suppress zeros with a custom number format, or hide them sheet-wide — plus the VLOOKUP-returns-0 fix.
How-toHow to sum if a cell contains specific text
Use SUMIF with wildcard criteria (*text*) to sum values in a column whenever a related column contains a specific substring.
How-toHow to write a nested IF formula
Chain multiple IF functions inside each other to test several conditions and return different results for each scenario.
How-toHow to write an IF with multiple conditions
Combine IF with AND or OR to test multiple conditions simultaneously and return a result based on whether all or any are true.