Google Sheets IF Cell Contains: Formulas and Examples

To check whether a Google Sheets cell contains particular text, combine IF, ISNUMBER, and SEARCH. For example, if the text to test is in A2, =IF(ISNUMBER(SEARCH("apple",A2)),"Yes","No") returns Yes when A2 contains “apple” anywhere, and No otherwise.

What “if cell contains” means in Sheets

“If cell contains” is not a single Google Sheets function. It describes a test: look for text or a pattern inside a cell, then return a result or take an action depending on whether the test succeeds. The right formula depends on what you want to do with that result.

For example, a spreadsheet might need to label descriptions that mention a product, flag notes containing the word “urgent,” count entries containing a phrase, or color matching rows. A formula can return a label or number; conditional formatting can change the appearance of cells. These are related tasks, but they use different tools.

One important distinction is that “contains” usually means a partial match. If A2 says “green apple,” it contains “apple.” An exact comparison, by contrast, would only match a cell whose entire value is “apple.”

Use IF with SEARCH for a basic contains test

SEARCH looks for one piece of text within another and returns the position where it begins. If it cannot find the text, it returns an error. ISNUMBER converts that outcome into a simple TRUE or FALSE test: a position is a number, while a failed search is not. IF then displays the result you choose.

With the text to inspect in A2 and the word apple as the search term, use =IF(ISNUMBER(SEARCH("apple",A2)),"Yes","No"). If A2 contains “Apple pie,” the result is Yes; if it contains “pear,” the result is No. SEARCH is not case-sensitive, so it finds “apple,” “Apple,” and “APPLE.”

The two results do not have to be words. With item descriptions in A2:A20, put this formula beside the first description and fill it down: =IF(ISNUMBER(SEARCH("apple",A2)),1,0). A row containing “apple juice” returns 1; a row containing “orange juice” returns 0. You can also return a blank for nonmatches by using an empty string as the second result: =IF(ISNUMBER(SEARCH("apple",A2)),"Match"," "). If you want a genuinely empty-looking result, use "" rather than a space.

SEARCH is useful for everyday checks because it accepts a literal phrase and ignores capitalization. If you need capitalization to matter, use FIND in the same pattern. With the text in A2, =IF(ISNUMBER(FIND("Apple",A2)),"Match","No match") matches “Apple” but not lowercase “apple.”

Count or test matches with COUNTIF

If the goal is to count cells that contain a phrase, COUNTIF is often simpler than building a separate IF formula for every row. Its asterisk wildcard stands for any number of characters, including none. Put the descriptions in A2:A20 and use =COUNTIF(A2:A20,"*apple*") to count cells with apple anywhere in their contents. A cell containing “apple” or “green apple” qualifies.

To make a TRUE-or-FALSE test for whether at least one cell in that range contains the phrase, place the same range and criterion in =COUNTIF(A2:A20,"*apple*")>0. The result is TRUE when one or more cells match, and FALSE when none do. COUNTIF matching is generally case-insensitive, which suits many text searches.

The asterisk is not the only wildcard: a question mark represents one character. For instance, a criterion such as "c?t" can match “cat” or “cot.” If you need to search for a literal asterisk, question mark, or tilde rather than use it as a wildcard, prefix the wildcard character with a tilde in the criterion. Wildcards are helpful for flexible matching, but they do not provide the same control as a regular expression.

Check for several words or conditions

To match either of two words, use OR around separate tests. With a description in A2, =IF(OR(ISNUMBER(SEARCH("apple",A2)),ISNUMBER(SEARCH("pear",A2))),"Fruit","Other") returns Fruit if the cell contains either term. This approach is easy to read when there are only a few search terms.

To require both terms, use AND instead. With a note in A2, =IF(AND(ISNUMBER(SEARCH("late",A2)),ISNUMBER(SEARCH("delivery",A2))),"Review","") returns Review only if the note contains both “late” and “delivery,” in any order. The words do not have to be next to each other.

If the requirement is that a cell contain any one of many alternatives, REGEXMATCH can be more compact. With descriptions in A2, =IF(REGEXMATCH(A2,"apple|pear|plum"),"Fruit","Other") checks for any of the three alternatives. The vertical bars mean “or” in a regular expression. REGEXMATCH uses RE2-style regular expressions in Google Sheets, so punctuation such as a period can have a special meaning; escape it with a backslash when you intend to match the punctuation literally.

Use the simplest method that clearly expresses the rule. SEARCH is often easier to maintain for a single phrase, OR is straightforward for a short list, and REGEXMATCH is convenient for a longer pattern. A more complex pattern can be harder for a future editor to understand, so document unusual regular-expression rules in a nearby note.

Highlight cells that contain text

If you want a visual highlight rather than a result in another cell, use conditional formatting. Select the cells or rows to format, then choose Format > Conditional formatting. Under “Format cells if,” select “Custom formula is,” enter a formula, choose the formatting style, and select Done.

Suppose descriptions are in A2:A20 and you want matching description cells highlighted. Apply the rule to A2:A20 and use =ISNUMBER(SEARCH("urgent",A2)). The formula checks each cell relative to the top row of the applied range. If you want to color the entire row across A2:D20 when the description in column A contains urgent, apply the rule to A2:D20 and use =ISNUMBER(SEARCH("urgent",$A2)). The dollar sign fixes the column reference at A while the row number can change for each row.

Conditional formatting changes how cells look; it does not insert a label or replace their values. If you need to filter, count, or use the result in another calculation, add a formula column instead. The conditional-formatting interface and the basic formulas described here are Google Sheets features; Excel has its own conditional-formatting interface and some different formula capabilities.

Choose the method that fits the task

Return a label or value: Use IF with ISNUMBER and SEARCH for one case-insensitive phrase, or FIND when capitalization matters.
Count matching cells: Use COUNTIF with asterisks around the search phrase.
Test whether a range has any matches: Compare the COUNTIF result with zero.
Highlight matches: Create a conditional-formatting rule with a custom formula.
Match a pattern or several alternatives: Use REGEXMATCH when its pattern syntax makes the rule clearer.

There is no need to use IF for every task. For example, COUNTIF already returns a count, so adding IF around it is only useful if you want to translate that count into a message such as “Found” or “Not found.” Keeping the formula focused makes it easier to check and adapt.

Common problems and ways to fix them

The formula says No even though the word appears: Check that the search term is spelled correctly and that the formula points to the intended cell. A search for “apple” will not find a different spelling such as “apples” unless the shorter text actually appears within it.
The capitalization is not being enforced: SEARCH ignores case. Replace it with FIND if uppercase and lowercase should count as different.
The formula shows an error: A bare SEARCH returns an error when it finds no match. Wrap it in ISNUMBER as shown above so that a failed search becomes FALSE rather than an error in the final test.
The test is matching more than intended: A partial match can catch a word inside a longer word. If you need a whole-word rule, a regular expression with word boundaries may help, but test it against examples from your actual data; punctuation and language can affect what counts as a boundary.
A wildcard criterion behaves unexpectedly: In COUNTIF, * and ? are wildcard characters. Use a tilde to escape a wildcard when you need to search for it literally.
The formula separator is rejected: Sheets may use different formula separators depending on spreadsheet locale. If the formula examples with commas are rejected, check the file’s locale settings; some locales use semicolons between arguments.
A cell looks blank but still returns a match: The cell may contain a space or other characters. Consider cleaning imported data or checking the source value before changing the search rule.

For dates, numbers, and other non-text values, think carefully about what representation you want to search. A displayed date can depend on its formatting, and a numeric value is not necessarily equivalent to the text a user sees. If the intended test is numeric—for example, whether a value is greater than 100—use a numeric comparison instead of searching its displayed digits.

Frequently Asked Questions

How do I check if a cell contains a word in Google Sheets?
With the value in A2, use =IF(ISNUMBER(SEARCH("word",A2)),"Yes","No"). Replace word with the text you want to find and adjust the two result labels as needed.

Is SEARCH case-sensitive in Google Sheets?
No. SEARCH is case-insensitive. Use FIND instead when uppercase and lowercase letters must be treated differently.

How do I count cells that contain a word?
With the cells to check in A2:A20, use =COUNTIF(A2:A20,"*word*"). The asterisks allow any characters before or after the word.

Can I highlight a cell if it contains specific text?
Yes. Select the target range, open Format > Conditional formatting, choose “Custom formula is,” and enter a formula such as =ISNUMBER(SEARCH("urgent",A2)), adjusted for the first cell in your applied range.

What is the difference between contains and equals?
A contains test looks for text anywhere inside a cell, so “green apple” contains “apple.” An exact comparison checks whether the whole cell value is the specified value; use that when partial matches should not count.

Why use SEARCH instead of COUNTIF?
SEARCH is useful when you want to combine a match test with IF or other logic for one cell. COUNTIF is convenient for counting matches in a range and supports wildcard criteria.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.