Question: What formula tells you if A1containsthe text “apple”?
This is a surprisingly tricky problem in Excel.
But FIND has an annoying quirk if itdoesn’tfind “apple”, it returns the #VALUE error.
Unfortunately, this error appears even if we wrap the FIND function in the IF function.
Nobody likes to see errors in their spreadsheets.
(There may be some good reason for this, but returning zero would be much nicer.)
What about theSEARCH function, which also locates the position of text?
Unlike FIND, SEARCH supports wildcards, and is not case-sensitive.
Maybe SEARCH returns FALSE or zero if the text isn’t found?Nope.
SEARCH also returns #VALUE when the text isn’t found.
So, what to do?
For many situations (e.g.
conditional formatting) a 1 or 0 result will be just fine.
Other examples
So what can you do with these kind of formulas?
Here are a few examples (with full explanations) to inspire you:
Logical confusion?
If you should probably brush up on how logical formulas work,see this video.
It’s kind of boring, but it runs through a lot of examples.
Other formulas
If you like formulas (who doesn’t?!
),we maintain a big list of examples.
