This is a surprisingly tricky problem in Excel. The “obvious” answer is to use the FIND function to “look” for the text, like this: Then, if you want a TRUE/FALSE result, add the IF function: 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: FIND returns the position of the text (if found), but #VALUE if not found. 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: Now ISNUMBER returns TRUE when FIND yields a number, and FALSE when FIND throws the error.
Another way with COUNTIF
If all that seems a little crazy, you can also the COUNTIF function to find text: 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.
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: Now we get TRUE if “apple” is found, FALSE if not:
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:
Count cells that contain specific text Sum cells that contain specific text Test a cell to see if contains one of many things Highlight cells that contain specific text Build a search box to highlight data (video)
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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
title: “How To Find Text With A Formula” ShowToc: true date: “2022-11-12” author: “Bennie Domke”
In this video we’ll look at how to locate the position of one text string inside another. Let’s take a look. Excel contains two functions that can help you locate the position of text inside other text. The first function is FIND. FIND takes three arguments: the text you’re looking for, the text you’re looking within, and an optional argument which indicates the starting point for the search. This argument defaults to “1” if you don’t supply your own value. Note that FIND is case sensitive. Now let’s look at how FIND works using the values in this table. Column B contains the text we’re looking for, Column C contains the text we’re looking within, and column D contains the start number. In Column E, I’ll add the FIND function and configure it to use these values. The first result is “1” because a capital “A” is the first character in Apple. We get a #VALUE error in the next example because there is no lowercase “a.” When we look for the letter “p” FIND returns “2”. That’s because FIND gets the position of the first occurrence when there is more than one. If you’re searching for multiple characters, you’ll get the position of the first character. For the letters “le” this is “4”. Since FIND is case-sensitive, we’ll get “12” if we look for “the” with a lowercase “t.” Switching to an uppercase “T” will return “1”. When you just want a simple test, instead of a position, you can wrap the FIND function in a function called ISNUMBER. The ISNUMBER function will return “TRUE” when FIND returns a number, and “FALSE” if not. On the next sheet, we have examples for the SEARCH function. SEARCH is very similar to FIND and uses exactly the same arguments. However, SEARCH is not case-sensitive and also supports “wildcards.” When looking for the letter “a” SEARCH returns “1” for both a lower case and uppercase “A.” Like FIND, SEARCH returns #VALUE if the text is not found. A lowercase “the” returns “1”. If you want to find the second occurrence of “the,” you could use a start number of “4” which effectively skips the first “the.” Finally, SEARCH supports “wildcards.” You can use a “?” to represent one character. So, “?at” returns “5” because it matches “cat.” You can use an asterisk () to match one or more characters. So, “20” matches “2010” and returns “7”.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
