Explanation

In this example, the goal is to test a value in a cell to see if it contains a specific substring . Excel contains two functions designed to check the occurrence of one text string inside another: the SEARCH function and the FIND function. The difference is that the SEARCH function supports wildcards but is not case-sensitive, while the FIND function is case-sensitive but does not support wildcards. Both functions return the position of the substring in the text as a number when it is found and a #VALUE! error when the substring is not found. However, it is not obvious how to get a TRUE or FALSE result when the goal is simply to test for the existence of the substring. The standard approach is to wrap these functions in the ISNUMBER function to force a TRUE or FALSE result. ISNUMBER will return TRUE for a numeric result (a match) and FALSE when the result is #VALUE! (no-match).

Excel 365 now supports regular expressions (regex), a powerful tool for pattern-matching. The REGEXTEST function offers a direct way to test for specific text with a TRUE or FALSE result. Because REGEXTEST uses regex, it can be configured to use precise patterns for advanced use cases. See below for basic examples.

SEARCH function (not case-sensitive)

The SEARCH function is designed to look inside a text string for a specific substring. If SEARCH finds the substring, it returns the position of the substring in the text as a number. If the substring is not found, SEARCH returns a #VALUE error. For example:

=SEARCH("p","apple") // returns 2
=SEARCH("z","apple") // returns #VALUE!

To force a TRUE or FALSE result, we use the ISNUMBER function . ISNUMBER returns TRUE for numeric values and FALSE for anything else. So, if SEARCH finds the substring, it returns the position as a number, and ISNUMBER returns TRUE:

=ISNUMBER(SEARCH("p","apple")) // returns TRUE
=ISNUMBER(SEARCH("z","apple")) // returns FALSE

If SEARCH doesn’t find the substring, it returns an error, which causes the ISNUMBER to return FALSE.

Wildcards

Although SEARCH is not case-sensitive, it does support wildcards (*?~). For example, the question mark (?) wildcard matches any one character . The formula below looks for a 3-character substring beginning with “x” and ending in “y”:

=ISNUMBER(SEARCH("x?z","xyz")) // TRUE
=ISNUMBER(SEARCH("x?z","xbz")) // TRUE
=ISNUMBER(SEARCH("x?z","xyy")) // FALSE

The asterisk (*) wildcard matches zero or more characters. This wildcard is not as useful in the SEARCH function because SEARCH already looks for a substring . For example, it might seem like the following formula will test for a value that ends with “z”:

=ISNUMBER(SEARCH("*z",text))

However, because SEARCH automatically looks for a substring , the following formulas all return 1 as a result, even though the text in the first formula is the only text that ends with “z”:

=SEARCH("*z","XYZ") // returns 1
=SEARCH("*z","XYZXY") // returns 1
=SEARCH("*z","XYZXY123") // returns 1
=SEARCH("x*z","XYZXY123") // returns 1

This means the asterisk () is not a reliable way to test for “ends with”. However, you can use the asterisk () wildcard like this:

=SEARCH("x*2*b","AAAXYZ123ABCZZZ") // returns 4
=SEARCH("x*2*b","NXYZ12563JKLB") // returns 2

Here we are looking for “x”, “2”, and “b” in that order, with any number of characters in between. Finally, you can use the tilde (~) as an escape character to indicate that the next character is a literal like this:

=SEARCH("~*","apple*") // returns 6
=SEARCH("~?","apple?") // returns 6
=SEARCH("~~","apple~") // returns 6

The above formulas use SEARCH to find a literal asterisk (*), question mark (?), and tilde (~) in that order.

FIND function (case-sensitive)

Like the SEARCH function, the FIND function returns the position of a substring in text as a number, and an error if the substring is not found. However, unlike the SEARCH function, the FIND function respects case:

=FIND("A","Apple") // returns 1
=FIND("A","apple") // returns #VALUE!

To make a case-sensitive version of the formula, just replace the SEARCH function with the FIND function in the formula above:

=ISNUMBER(FIND(substring,A1))

The result is a case-sensitive search:

=ISNUMBER(FIND("A","Apple")) // returns TRUE
=ISNUMBER(FIND("A","apple")) // returns FALSE

REGEXTEST function (very powerful)

The REGEXTEST function tests for text defined by a given pattern. Regex patterns are very flexible and can be configured to match numbers, email addresses, dates, and other values that have an identifiable pattern. The generic syntax for REGEXTEST looks like this:

=REGEXTEST(text,pattern)

The text is the text to search within, and the pattern is the text to search for, which can be a hardcoded text string or a combination of special characters used to define regex patterns. The result from REGEXTEST is TRUE or FALSE, so there is no need for ISNUMBER:

=REGEXTEST("apple","a") // returns TRUE
=REGEXTEST("apple","z") // returns FALSE

Note that REGEXTEST is case-sensitive by default:

=REGEXTEST("apple","A") // returns FALSE

The power of REGEXTEST comes from its ability to use regex patterns. Here are a few simple examples that check text in cell A1 for various things:

=REGEXTEST(A1,"[0-9]") // test for a number
=REGEXTEST(A1,"[A-Z]") // test for an uppercase character
=REGEXTEST(A1,"\d{3}") // test for a 3-digit number
=REGEXTEST(A1,"[A-Z]{3}") // test for 3 uppercase characters together

Here are the examples above applied to a text string:

=REGEXTEST("apple9","[0-9]") // returns TRUE
=REGEXTEST("appLe","[A-Z]") // returns TRUE
=REGEXTEST("apple123","\d{3}") // returns TRUE
=REGEXTEST("appleABC","[A-Z]{3}") // // returns TRUE

For an overview of regex in Excel, see Regular Expressions in Excel .

If cell contains

To return a custom result when a cell contains specific text, you can wrap the formulas above inside the IF function like this:

=IF(ISNUMBER(SEARCH(substring,A1)),"Yes","No")
=IF(ISNUMBER(FIND(substring,A1)),"Yes","No")
=IF(REGEXTEST(A1,substring),"Yes","No")

Instead of returning TRUE or FALSE, the formulas above will return “Yes” if the substring is found and “No” if not. You are free to customize the values returned by IF as you like.

Test for more than one search string

To test a cell for more than one thing (i.e. for one of many substrings), see this example formula .

Explanation

In this example, the goal is to test the text in a cell and return TRUE if it contains one or more specific words, and FALSE if not. Notice the emphasis here is on words, not substrings. For example, if we are testing for the word “green” and the text contains the word “evergreen” but not the word “green” the formula should return FALSE. Traditionally, this has been a difficult problem in Excel because there has not been a simple way to parse text into words. However, with the introduction of the TEXTSPLIT function, it is easy to split a text string directly into an array of words. Once we have the words in an array, they can be tested.

Note: The TEXTSPLIT function is only available in the most recent version of Excel. In older versions of Excel, you can use a similar formula that checks for a substring.

  • Using TEXTSPLIT to split text into words
  • Testing for words
  • Cell contains specific words
  • Cell contains one of many words
  • Cell contains at least 2 words
  • Cell contains all words
  • Cell contains some words but not others

Using TEXTSPLIT to split text into words

The TEXTSPLIT function is designed to split text into an array with a given delimiter. For example, in the formula below, we use TEXTSPLIT and a space (" “) as a delimiter to split the text “The sea is blue’’ into 4 words:

=TEXTSPLIT("The sea is blue"," ") // returns {"The","sea","is","blue"}

Once we have the words in an array, we can do all kinds of things with them. We can count words, compare words, check for specific words, etc. TEXTSPLIT works very well for this task, but there is one complication you must be aware of: words are not always separated just by spaces. For example, what happens if we try the formula above on the text “The flag is red, green, and blue”:

=TEXTSPLIT("Red, green, and blue"," ")

In this case, the array returned by TEXTSPLIT looks like this:

{"Red,","green,","and","blue"}

Notice that the commas after “red” and “blue” are part of the output. This is bad. We don’t want punctuation to be included with the words, because it will cause problems later when we try to analyze the words. The solution is to expand the list of delimiters provided to TEXTSPLIT to include punctuation when needed. In this case, we simply need to add a comma (”,”) like this:

=TEXTSPLIT("Red,green, and blue",{","," "})

Note: we provide both the comma and the space using an array constant like {","," “}, which is a convenient way to supply more than one hard-coded value at the same time.

Now we are asking TEXTSPLIT to split words separated by a comma (”,") and a space (" “). This works fine, but notice the result now contains an extra empty string as the third item in the array:

{"Red","green","","and","blue"}

This happens because TEXTSPLIT now splits the text at both delimiters . This is also bad, although, depending on the use case, it may not matter. Either way, we can easily remove the empty values by setting ignore_empty to 1:

=TEXTSPLIT("Red,green, and blue",{","," "},,1)

Notice we need to skip over and omit the row_delimiter argument. The revised formula now gives us the result we want, an array that contains four words without any punctuation or empty values:

{"Red","green","and","blue"}

Note: you will need to adjust the delimiters provided to TEXTSPLIT to suit the text you are working with.

We now have the core process we need to begin testing a cell for specific words.

Testing for words

Now that we have an array of words, the next step in this process is to check those words against our word(s) of interest. In the worksheet shown, we are looking for the word “green”. There are different ways to go about this in Excel. For example, we could count instances of the word “green” in the array returned by TEXTSPLIT. However, a more scalable approach is to use the XMATCH function to check the result from TEXTSPLIT.

XMATCH is an upgrade to the MATCH function , and it returns the numeric location of a lookup value in an array of data. One nice feature of XMATCH is that it automatically performs an exact match, which is what we want in this case. To use XMATCH to look for “green” in the output from TEXTSPLIT, we use a formula like this:

XMATCH("green",TEXTSPLIT("Red, green, and blue",{".",", "," "}))

Notice we are using TEXTSPLIT as explained above, but we have embedded the TEXTSPLIT function inside the XMATCH function as the lookup array, with “green” as the lookup value.

XMATCH("green",TEXTSPLIT(B5,{".",","," "},,1)) 

After TEXTSPLIT runs, we have the following:

=XMATCH("green",{"Red","green","and","blue"}) // returns 2

Because “green” appears as the second value in the array returned by TEXTSPLIT, the XMATCH function returns 2 as a result. What happens if we check for a non-existent value, for example, the word “pink”? In that case, XMATCH returns an #N/A error:

=XMATCH("pink",{"Red","green","and","blue"}) // returns #N/A

To recap: when XMATCH finds a value, it will return a numeric position. When XMATCH does not find a value, it will return an #N/A error. At this point, we have a basic mechanism in place to check for a specific word in a cell. The only remaining task is to return TRUE or FALSE. The simplest way to do this is to use the ISNUMBER function like this:

=ISNUMBER(XMATCH("green",{"Red","green","and","blue"})) // returns TRUE
=ISNUMBER(XMATCH("pink",{"Red","green","and","blue"})) // returns FALSE

This works fine, but a more general approach, which will scale better to handle other related problems, is to use the COUNT function like this:

=COUNT(XMATCH("green",{"Red","green","and","blue"})) // returns 1
=COUNT(XMATCH("pink",{"Red","green","and","blue"})) // returns 0

The COUNT function only counts numeric values. So, when XMATCH returns a number, COUNT will return a positive number. When XMATCH returns an error, COUNT will return zero. To get a TRUE or FALSE result we can check to see if the count is greater than zero.

=COUNT(XMATCH("green",{"Red","green","and","blue"}))>0 // TRUE
=COUNT(XMATCH("pink",{"Red","green","and","blue"}))>0 // FALSE

We now have all the pieces we need to test for specific words in a cell.

Cell contains specific words

Returning to the example shown in the worksheet above, the goal is to test for a given word and return TRUE or FALSE. This is accomplished by a formula like this in cell D5:

=COUNT(XMATCH("green",TEXTSPLIT(B5,{".",","," "},,1)))>0

Working from the inside out, TEXTSPLIT is configured to split the text in cell B5 using three delimiters supplied in an array constant like this:

TEXTSPLIT(B5,{".",", "," "},,1)
  • text - cell B5
  • col_delimiter - {”.",","," “}
  • row_delimiter - omitted
  • ignore_empty - 1 (equivalent to TRUE)

Note that we provide three separate delimiters: a period (”."), a comma (",") and a space (" “). We also set ignore_empty to TRUE by providing 1. This is an important detail. We want TEXTSPLIT to ignore empty values because the delimiters will sometimes split text in a way that results in empty strings (”"). Enabling the ignore empty behavior will remove any empty values that creep into the output from TEXTSPLIT.

In cell D5, the result from TEXTSPLIT is an array of seven words like this:

{"The","brown","dog","lay","on","green","grass"}

Next, since “green” appears as the sixth word, the XMATCH function returns 6, COUNT returns 1, and the final result is TRUE:

=COUNT(XMATCH("green",{"The","brown","dog","lay","on","green","grass"}))>0
=COUNT(2)>0
=1>0
=TRUE

Note: XMATCH is not case-sensitive, so testing for “Green” or “green” will return the same result.

Cell contains one of many words

Another common challenge is to test a cell for one of many words, as seen in the worksheet below. Here, the goal is to test text values in column B to see if they contain any one of the three words that appear in the range F5:F7, which is named “words”.

Testing for one of many specific words - 1

How should we adjust the formula to handle more than one lookup value? As it turns out, we can use the same formula we used above, replacing the text “green” with the named range words (F5:F7):

=COUNT(XMATCH(words,TEXTSPLIT(B5,{".",","," "},,1)))>0

The difference is that the named range words contains three words, which Excel represents in an array like this:

{"red";"blue";"green"}

Note: The named range is optional but provides some nice conveniences: it automatically behaves like an absolute reference and it makes the formula more readable. If you prefer, you can use a regular absolute reference ($F$5:$F$7) instead.

Because we are now giving XMATCH three separate lookup values, it will return 3 results like this:

{#N/A;#N/A;6}

The first #N/A error tells us that “red” was not found. The second #N/A error indicates that “blue” was not found. The last result, 6, tells us that the word “green” appeared as the sixth word in the array returned by TEXTSPLIT. The COUNT function only counts numbers so it returns 1 and the final result is TRUE. You can increase or decrease the number of words provided to XMATCH and the formula will continue to work correctly.

Note: this formula will return TRUE if any number of words is found.

Cell contains at least 2 words

Now that we have the basic formula working, we can easily adjust the logic to suit more specific use cases. For example, we can require the cell to contain at least 2 of the words provided, as seen in the screen below:

Testing for at least 2 of many words - 2

In this worksheet, the formula in cell D5 now looks like this:

=COUNT(XMATCH(words,TEXTSPLIT(B5,{".",","," "},,1)))>=2

Notice that this formula is almost exactly the same as the formula above. The only difference is that we are checking the result from COUNT with >=2, to require that there be at least two matching words in cell B5. In cell B5, the result is FALSE because only the word “red” is found in the text, as explained above. However, in cell D7, the result is TRUE, since both “red” and “blue” appear in cell B7, so COUNT returns 2. The formula in cell B7 evaluates like this:

=COUNT(XMATCH(words,TEXTSPLIT(B7,{".",","," "},,1)))>=2
=COUNT({4;7;#N/A})>=2
=2>=2
=TRUE

Cell contains all words

How can we adjust the formula to require that all given words appear in a cell? This can be done with another small adjustment, as seen in the worksheet below:

Testing for all of many words - 3

The formula in cell D5, copied down, looks now like this:

=COUNT(XMATCH(words,TEXTSPLIT(B5,{".",","," "},,1)))=COUNTA(words)

In this formula, we compare the count of “hits” returned by COUNT to the count of words returned by the COUNTA function . When the counts are equal, it means we found all given words in the text and the formula returns TRUE. When the counts are not equal, it means at least 1 word wasn’t found and the formula returns FALSE. We use COUNTA to count the words because COUNTA will count both numbers and text values.

Cell contains some words but not others

As a last example, let’s look at how to test a cell for certain specific words while at the same time specifically excluding others. This is the behavior we want:

  1. If a cell contains specific words of interest and does not contain other specific words, the result should be TRUE
  2. If a cell contains specific words of interest and does contain other specific words, the result should be FALSE

This makes the problem a bit more challenging, but we can still use the same approach described above. To make the formula more efficient and easier to read, we’ll add the LET function to the mix. LET will enable us to parse the source text into words just one time and store the result in a variable where it can be reused.

In the worksheet below, we are testing the text in column B for the words “red”, “blue”, and “green” while at the same time excluding cells that contain the words “pink” or “brown”:

Testing for specific words while excluding others - 4

Notice that we have also added a named range to the worksheet: The range F5:F7 is named “words” as before and the range F12:F13 is now named “xwords”. The idea is that xwords contains words that we want to exclude. The formula in cell D5 looks like this:

=LET(
  source,TEXTSPLIT(B5,{".",","," "},,1),
  AND(
    COUNT(XMATCH(words,source))>0,
    COUNT(XMATCH(xwords,source))=0)
)

In the first line of the formula, we use TEXTSPLIT as described above to create an array of words from the text in cell B5, which is assigned to a variable called “source”:

source,TEXTSPLIT(B5,{".",","," "},,1),

Next, we use the AND function to check the results of two expressions. The first expression checks that we have at least one word of interest in the cell:

COUNT(XMATCH(words,source))>0

The second expression checks that we have zero words not of interest in the cell:

COUNT(XMATCH(xwords,source))=0)

The AND function will only return TRUE when both of the expressions above return TRUE. If either expression is FALSE, the result will be FALSE. This accomplishes our goal: the formula will return TRUE only when at least one word from words is found and zero words from xwords are found.