Google Sheets IF String Contains: Formulas and Examples

To make Google Sheets return one value when a cell contains text and another when it does not, combine IF with SEARCH and ISNUMBER: =IF(ISNUMBER(SEARCH("apple", A2)), "Yes", "No"). This checks whether A2 contains “apple” anywhere, without distinguishing uppercase from lowercase.

How the basic formula works

Sheets does not have a dedicated IFCONTAINS function. Instead, the formula uses one function to look for the text and another to turn the result into a useful answer. SEARCH returns the character position where the requested text begins. If it cannot find that text, it returns an error. ISNUMBER tests whether the search result is a number, and IF chooses which output to show.

Assuming A2 contains the product description “Fresh apple juice,” this formula returns “Yes”: SEARCH finds “apple” beginning at position 7. If A2 contains “Orange juice,” the search fails, ISNUMBER evaluates to FALSE, and the formula returns “No.” The search can match a word inside a longer word, too: looking for “app” matches “apple.” That is useful for fragments, but it may be too broad when you need a whole-word match.

For a reusable pattern, replace the example text with the phrase to find and change A2 to the cell you want to inspect. The two outputs can be labels, numbers, or other text. For example, you might return “Review” and “OK,” or 1 and 0, depending on what a later formula or chart needs.

Choosing between SEARCH, FIND, and COUNTIF

Use SEARCH for a typical “does this text appear?” test. It ignores capitalization, so “APPLE,” “Apple,” and “apple” all match. The FIND function has a similar purpose but is case-sensitive. For a simple check against a cell or range with wildcard patterns, COUNTIF is another practical option.

SEARCH: Finds text inside other text and ignores letter case. It is a good default for ordinary keyword checks.
FIND: Finds text inside other text but requires the same capitalization. Use it when capitalization has meaning in your data.
COUNTIF: Counts cells matching a condition. Asterisks can stand for any number of characters, so a pattern with an asterisk on each side checks for a substring.
REGEXMATCH: Checks text against a regular expression. It supports more elaborate patterns, but regex symbols need care.

For example, with the text “Urgent: call the supplier” in A2, this case-sensitive version returns “Match” only if the searched word has the same capitalization as the cell text. Assuming A2 contains that example text, the result is “Match”:

=IF(ISNUMBER(FIND("Urgent", A2)), "Match", "No match")

If the text in A2 instead starts with lowercase “urgent,” the same formula returns “No match.” That difference is the main reason to choose FIND rather than SEARCH.

Using COUNTIF with a contains pattern

When you want a count rather than a label, COUNTIF can count cells that contain a phrase. Put an asterisk before and after the phrase: the first allows any characters before it, and the second allows any characters after it. With “apple” anywhere in a cell as the criterion, cells containing “green apple,” “apple juice,” or just “apple” qualify.

Assuming descriptions are in A2:A6, with “apple” appearing in three of those cells, this returns 3:

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

To turn that test into an IF result for one cell, use a one-cell range. Assuming A2 contains “Apple juice,” this returns “Yes” because the pattern matches:

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

COUNTIF matching is not case-sensitive. One detail to watch is that asterisks and question marks have special meanings in wildcard criteria: an asterisk matches any number of characters, while a question mark matches one character. If the text you are searching for includes those characters and you need them treated literally, COUNTIF criteria require wildcard escaping with a tilde. For ordinary words, this complication does not apply.

Checking case, whole words, and multiple terms

SEARCH and FIND look for a sequence of characters, not a word boundary. A search for “art” can match “carton,” “artist,” and “art.” If that is not the intended meaning, a regular expression can check for a word boundary. In Google Sheets, REGEXMATCH uses RE2 regular-expression syntax. Assuming A2 contains “The art show,” this formula returns “Whole word found”:

=IF(REGEXMATCH(A2, "(?i)\bart\b"), "Whole word found", "Not found")

The (?i) flag makes this expression case-insensitive, and \b marks a word boundary in the regular expression. Because a backslash is itself special in a Sheets string, the formula uses two backslashes to pass one to the regex engine. Regex patterns can be powerful, but test them on representative examples before using them across important data.

To check for either of two phrases with SEARCH, make each individual search safe with IFERROR. Assuming A2 contains “Pear tart,” this returns “Fruit item” because the second term is found:

=IF(OR(IFERROR(SEARCH("apple", A2), 0)>0, IFERROR(SEARCH("pear", A2), 0)>0), "Fruit item", "Other")

For additional alternatives, add another IFERROR(SEARCH(...), 0)>0 condition inside OR. This matters because a missing search term normally produces an error; wrapping each search avoids letting one missing term prevent the other test from being evaluated as intended.

Applying a contains test to many rows

For a short list, enter the formula in the first result row and fill it down. If descriptions are in A2:A100 and you put the formula in B2, use the single-cell reference A2; when you copy the formula down, Sheets adjusts it to A3, A4, and so on. You can drag the small square at the lower-right corner of the selected cell, or copy and paste the formula into the remaining result cells.

For a column-wide result without copying the formula, use ARRAYFORMULA. Assuming descriptions are in A2:A and you enter this in B2, rows with no description remain blank; a row containing “apple” gets “Yes,” and a nonmatching populated row gets “No”:

=ARRAYFORMULA(IF(A2:A="", "", IF(IFERROR(SEARCH("apple", A2:A), 0)>0, "Yes", "No")))

Keep the output column clear beneath B2 so the array results have room to expand. If another value is already in the output range, Sheets may show an expansion error instead of filling the column. For very large sheets, consider limiting the source range rather than using an entire column, particularly if the sheet contains many calculations.

Highlighting cells that contain a string

If the goal is visual highlighting rather than a result in another column, use conditional formatting. Select the cells to format, then choose Format > Conditional formatting. In the conditional-formatting panel, set the rule to Custom formula is, enter a formula, and choose a formatting style. Assuming the selected range is A2:A100, this formula highlights cells in that range that contain “urgent,” without regard to case:

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

The reference uses A2 because it is the first cell in the selected range. Sheets adjusts the row reference for each cell being evaluated. If you are applying the rule to a different range or want to test a separate column, change the reference to the appropriate starting cell. Excel has a similar conditional-formatting feature, but its menu path and rule interface differ; the steps above are for Google Sheets in a web browser.

Common problems and useful fixes

The formula displays an error when there is no match: SEARCH returns an error when it cannot find the phrase. Use ISNUMBER(SEARCH(...)) for a TRUE/FALSE test, or wrap SEARCH in IFERROR when you need a numeric fallback.
The formula matches more than expected: SEARCH looks for a substring, not a complete word. Use a suitable REGEXMATCH word-boundary pattern if whole-word matching is required.
Capitalization seems to be ignored: That is normal for SEARCH and COUNTIF. Use FIND for a case-sensitive substring check.
The formula checks the wrong cell after copying: Relative references such as A2 shift when copied. If every formula should inspect the same cell or column, use dollar signs where appropriate, such as $A2 to lock the column while allowing the row to change.
A conditional-formatting rule highlights the wrong rows: Check that the formula refers to the first row of the selected range. A rule applied to A2:A100 should generally start with A2, not A1.
A formula copied from a website will not parse: Check that quotation marks are straight quotes and that commas match your spreadsheet’s locale settings. Some locales use semicolons as argument separators.

Also check for leading or trailing spaces if the results seem inconsistent. A search for “apple” still finds the word inside a cell with extra spaces around it, but a search phrase that accidentally includes a space may not match the same text. If the search term comes from another cell, verify that cell’s contents and spelling before changing the formula.

Frequently Asked Questions

What is the Google Sheets formula for “if cell contains”?
For a case-insensitive check, use =IF(ISNUMBER(SEARCH("text", A2)), "Yes", "No"), replacing “text” and A2 with your phrase and source cell. SEARCH looks anywhere in the cell, not just at the beginning.

Is there a CONTAINS function in Google Sheets?
No. For a typical substring test, combine SEARCH with ISNUMBER or use COUNTIF with asterisks around the search text. REGEXMATCH is an option when the match needs a more specific pattern.

How do I make the check case-sensitive?
Replace SEARCH with FIND. FIND distinguishes uppercase and lowercase letters, while SEARCH does not. Keep ISNUMBER around FIND if you want a TRUE/FALSE result instead of an error when the text is absent.

How do I check whether a cell contains one of several words?
Use OR with a separate error-safe SEARCH test for each term. For example, wrap each SEARCH in IFERROR and compare its fallback result with zero, as in the multiple-term example above.

Can I highlight cells that contain a word without adding a formula column?
Yes. Select the range, choose Format > Conditional formatting, select “Custom formula is,” and enter a formula such as =ISNUMBER(SEARCH("urgent", A2)) when A2 is the first cell in the selected range.

Why does searching for a word match part of another word?
SEARCH checks for the requested character sequence wherever it appears. To avoid matching inside longer words, use a REGEXMATCH pattern with word boundaries, and verify that the pattern behaves correctly for punctuation and the language in your data.

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.