How to Return a Value if a Cell Contains Specific Text in Excel
Check Cell for Text is a Excel function that checks if a specified text exists within a cell and returns one value if true and another if false.. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Struggling to check if a cell contains certain text? Use the IF function combined with SEARCH to easily return desired values.
The Formula
"Return a value if a cell contains a specific word or partial text, using IF combined with SEARCH or FIND"
=IF(ISNUMBER(SEARCH("text", A1)), "Value if true", "Value if false")This formula checks if a specified text exists within a cell and returns one value if true and another if false.
Step-by-Step Breakdown
- SEARCH function looks for the specified text within the target cell.
- ISNUMBER checks if the SEARCH function returns a valid position (number).
- IF evaluates the result of ISNUMBER to determine which value to return.
- Specify the text you are searching for and the cell reference.
Edge Cases & Warnings
- The search text is case-sensitive when using FIND instead of SEARCH.
- The formula returns an error if the cell is empty.
- If the search text is not found, the formula returns the 'Value if false'.
Examples
"A1 contains 'Hello World', search for 'World'"
'Value if true'"A1 contains 'Goodbye', search for 'Hello'"
'Value if false'Frequently Asked Questions
Can I use this formula for partial text?
Yes, the SEARCH function allows for partial matches.
What happens if the text is not found?
The formula will return the specified 'Value if false'.
Is this formula case-sensitive?
No, SEARCH is not case-sensitive, but FIND is.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.