Excel IF Text Contains a Word: Formulas and Examples

To check whether a cell contains a word or phrase in Excel, use =IF(ISNUMBER(SEARCH("apple",A2)),"Yes","No"). It returns Yes when A2 contains “apple” anywhere in its text, without distinguishing uppercase from lowercase; use FIND instead of SEARCH if the match should be case-sensitive.

The basic IF and SEARCH formula

SEARCH looks for one text string inside another and returns the position where it begins. ISNUMBER turns that position into TRUE, or turns SEARCH’s not-found error into FALSE. IF then displays the result you choose for each outcome. The cell reference A2 is just an example: replace it with the cell you want to check, and replace apple with your target word or phrase.

With product descriptions in A2:A20, and with the word “apple” in the example text in A2, enter this formula in B2 and fill it down. If A2 contains “Green apple juice,” the result is Yes; if it contains “Orange juice,” the result is No.

=IF(ISNUMBER(SEARCH("apple",A2)),"Yes","No")

SEARCH is not case-sensitive, so “APPLE,” “Apple,” and “apple” all match. The formula checks for a sequence of characters, not necessarily a separate word: it also finds apple in “pineapple.” That distinction matters when your question is whether a cell contains a particular word as a standalone word rather than merely those letters.

What each function does

The formula works by combining three functions, each with a different job:

SEARCH(“apple”,A2) looks for apple inside A2. If it starts at the first character, it returns 1; if it starts later, it returns a higher position number. If it is absent, SEARCH returns an error.
ISNUMBER(…) returns TRUE when SEARCH produces a number and FALSE when SEARCH produces an error.
IF(…) displays the first result when the test is TRUE and the second when it is FALSE. Here those results are the text labels Yes and No.

The result labels are optional. You can replace them with other text, numbers, or cell references. For example, a formula can return a category, a status, or a blank cell when there is no match. Put text results in straight double quotation marks; a blank result is written as two quotation marks with nothing between them.

SEARCH versus FIND and COUNTIF

SEARCH is a good default when capitalization should not matter. FIND performs a similar substring search but is case-sensitive. For example, the search term apple would not match Apple with FIND. With product descriptions in A2:A20 and the test in A2, this formula returns Yes only if lowercase apple appears with the same capitalization; for “Apple juice,” it returns No.

=IF(ISNUMBER(FIND("apple",A2)),"Yes","No")

COUNTIF is another option for a simple check. It supports asterisks as wildcards, where * stands for any number of characters. With descriptions in A2:A20 and the value being checked in A2, this formula also returns Yes for “Green apple juice” and No for “Orange juice.” Like SEARCH, it is not case-sensitive and matches apple within pineapple.

=IF(COUNTIF(A2,"*apple*")>0,"Yes","No")

For most formulas that need to return a label for an individual cell, SEARCH with ISNUMBER makes the test’s meaning clear. COUNTIF can be convenient when you want a count across a range, while FIND is useful when capitalization is part of the requirement. The wildcard characters in COUNTIF have special meaning; if the target itself contains an asterisk or question mark, use a different approach or escape the character with a tilde.

Match a whole word, not part of a word

The basic formula finds characters anywhere in the cell, so it cannot tell whether the match is a complete word. A quick workaround is to add spaces around both the search term and the cell text. With a sentence in A2, this formula returns Yes for “I like apple pie” but No for “pineapple pie.”

=IF(ISNUMBER(SEARCH(" apple "," "&A2&" ")),"Yes","No")

This space-boundary method has a limitation: it will not recognize apple next to punctuation, as in “apple,” or at the start of a sentence followed immediately by a comma. It is suitable only when the data uses spaces around words consistently. There is no single simple SEARCH formula that reliably handles every possible punctuation mark, apostrophe, hyphen, and variation in spacing.

For text with varied punctuation, decide which characters count as word separators and normalize them before searching. A helper column can replace common punctuation marks with spaces, after which a whole-word check can use the space-boundary method. For more complex data-cleaning tasks, Power Query can transform text as part of a repeatable import process. Check the result against examples from your actual data, especially hyphenated terms and punctuation at the beginning or end of a cell.

Return a category, a blank, or a count

Yes and No are not required outputs. With descriptions in A2:A20 and the description being tested in A2, this formula labels matching rows “Fruit” and leaves other rows blank. The result for “Green apple juice” is Fruit; for “Orange juice,” it is an empty-looking cell. A blank result can make a report easier to scan, but it can also hide the fact that a row was checked.

=IF(ISNUMBER(SEARCH("apple",A2)),"Fruit","")

To count how many cells in a range contain the target text, use COUNTIF with wildcards rather than copying an IF formula and adding the results manually. With descriptions in A2:A20, this returns the number of cells containing apple anywhere in their text. If the example range contains “Green apple juice,” “Pineapple,” and “Orange juice,” the result is 2 because both of the first two cells contain that character sequence.

=COUNTIF(A2:A20,"*apple*")

COUNTIF counts matching cells, not every occurrence within a cell. If one description says “apple, apple, and pear,” it still contributes just one to the count. Also, the wildcard count follows the same partial-match behavior: it counts pineapple unless you have taken additional steps to test for whole words.

Check for one of several words

Sometimes a cell should match if it contains any one of several terms. Use OR to combine separate SEARCH tests. With descriptions in A2:A20 and the test in A2, this example returns “Match” if A2 contains apple or pear, regardless of capitalization. It returns No match if neither appears.

=IF(OR(ISNUMBER(SEARCH("apple",A2)),ISNUMBER(SEARCH("pear",A2))),"Match","No match")

For a short list, separate tests are readable and easy to edit. For a long or frequently changing list of keywords, maintaining a very long formula becomes cumbersome. Keeping keywords in a worksheet range and using a more advanced formula or a data-transformation step may be easier to maintain. The appropriate approach depends on your Excel version and whether you need a simple yes/no result, a matched keyword, or a count.

Highlight cells that contain a word

If the goal is visual rather than a result in another column, conditional formatting can highlight matching cells. In Excel for Microsoft 365 on Windows, select the range to format, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With A2:A100 selected and A2 as the active cell, enter the following rule formula, choose a format, and confirm. The relative reference adjusts for each cell in the selected range.

=ISNUMBER(SEARCH("apple",A2))

This highlights any selected cell containing apple, including a cell containing pineapple, because the rule uses a substring search. Choose Home > Conditional Formatting > Manage Rules to review or edit rules. The conditional-formatting commands are also available in Excel for Mac, though the dialog layout can differ. Excel for the web has conditional-formatting options too, but some rule-editing capabilities can vary from the desktop app.

Common problems and how to fix them

The formula finds a word inside another word: SEARCH checks for a character sequence. Use a whole-word strategy, such as normalized text with spaces around tokens, if that is the actual requirement.
Capitalization seems to be ignored: That is expected with SEARCH and COUNTIF. Use FIND when you need a case-sensitive test.
The formula displays an error: Check that the referenced cell contains the expected data and that the formula uses straight quotation marks, balanced parentheses, and commas between arguments. If the source cell itself contains an error, the search can pass that error along.
The formula returns No for a word that looks present: Look for extra spaces, punctuation, nonbreaking spaces, or a spelling difference. Imported text can contain characters that look like ordinary spaces but are not the same character.
The formula shows as text instead of calculating: Make sure the cell is not formatted as Text, and that the formula begins with an equals sign. If you change a Text-formatted cell, you may need to re-enter the formula after changing its number format.

When results affect a report, test both a known match and a known nonmatch, along with edge cases such as uppercase text, punctuation, and the target embedded in a longer word. A formula can be syntactically correct and still implement the wrong definition of “contains.”

Frequently Asked Questions

Is there an Excel IF CONTAINS function?
No. Excel does not have a standard IF CONTAINS function. Combine IF with SEARCH and ISNUMBER to test for a substring, or use a different method when you need whole-word matching.

Does Excel SEARCH ignore uppercase and lowercase?
Yes. SEARCH is case-insensitive. Use FIND if uppercase and lowercase must be treated differently.

How do I check whether a cell contains one of several words?
For a short list, combine ISNUMBER(SEARCH(…)) tests inside OR, then use that result as IF’s logical test. This checks whether any listed term appears.

How do I return a blank when the word is not found?
Use an empty text value as IF’s false result, as in =IF(ISNUMBER(SEARCH("apple",A2)),"Found",""). With a description in A2, the formula displays Found for a match and otherwise displays a blank.

Can I find a whole word rather than a partial match?
Yes, but the simple substring formula does not enforce word boundaries. Adding spaces around the text can work when words are separated consistently by spaces; punctuation and more varied text usually require normalization or a more specialized method.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.