Exceljet

Quick, clean, and to the point

How to find text with a formula

by Dave Bruns | April 29, 2016

Question: What formula tells you if A1 contains the text "apple"?

This is a surprisingly tricky problem in Excel. The "obvious" answer is to use the FIND function to "look" for the text, like this:

=FIND("apple",A1)

Then, if you want a TRUE/FALSE result, add the IF function:

=IF(FIND("apple",A1),TRUE)

This works great if "apple" is found – FIND returns a number to indicate the position, and IF calls it good and returns TRUE.

But FIND has an annoying quirk – if it doesn't find "apple", it returns the #VALUE error.  This means that the formula above doesn't return FALSE when text isn't found, it returns #VALUE:

Finding text with the FIND function
FIND returns the position of the text (if found), but #VALUE if not found.

Finding text with the FIND function with IF function
Unfortunately, this error appears even if we wrap the FIND function in the IF function.

Grrrr. 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 the SEARCH 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? Well, in a classic, counter-intuitive Excel move, you can trap the #VALUE error with the ISNUMBER function, like this:

=ISNUMBER(FIND("apple",A1))

Now ISNUMBER returns TRUE when FIND yields a number, and FALSE when FIND throws the error.

Trapping the #VALUE error with the ISNUMBER function

Another way with COUNTIF

If all that seems a little crazy, you can also the COUNTIF function to find text:

=COUNTIF(A1,"*apple*")

It might seem strange to use COUNTIF like this, since we're just counting one cell. But COUNTIF does the job well – if "apple" is found, it returns 1, if not, it returns zero.

Finding text with COUNTIF and wildcards

For many situations (e.g. conditional formatting) a 1 or 0 result will be just fine. But if you want to force a TRUE/FALSE result, just wrap with IF:

=IF(COUNTIF(A1,"*apple*"),TRUE)

Now we get TRUE if "apple" is found, FALSE if not:

Finding text with COUNTIF plus IF

Note that COUNTIF supports wildcards – in fact, you must use wildcards to get the "contains" behavior, by adding an asterisk to either side of the text you're looking for. On the downside, COUNTIF isn't case-sensitive, so you'll need to use FIND if case is important.

Other examples

So what can you do with these kind of formulas? A lot!

Here are a few examples (with full explanations) to inspire you:

Logical confusion?

If you need to 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.

I have been on your emailing list and your tips have made sense to me so I thought that would take the next step and really try and up skill myself with excel. - Maara
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course