Google Sheets If Contains: A Practical Guide to Substring Matching
Learn how to check if a string contains a substring in Google Sheets using IF with SEARCH, FIND, or REGEXMATCH. Includes examples, edge cases, and practical tips for reliable contains checks across sheets and reports.
Google Sheets doesn't have a literal CONTAINS function, so you test substrings inside IF by pairing SEARCH or FIND with ISNUMBER, or use REGEXMATCH for flexible matching. For example, =IF(ISNUMBER(SEARCH("apple", A2)), "Yes", "No") returns Yes when A2 contains apple. For case-insensitive checks, use SEARCH or REGEXMATCH with (?i) flags; for case-sensitive, use FIND.
What does 'IF contains' mean in Google Sheets?
In Google Sheets, 'IF contains' describes a pattern where you check whether a text cell includes a given substring, and then return one value if true and another if false. Since Sheets doesn't have a dedicated CONTAINS function, you combine IF with text-search functions like SEARCH, FIND, or REGEXMATCH. This approach is robust for simple and complex substrings and works well when data quality varies across rows. You can also nest contains logic inside other functions such as VLOOKUP or FILTER to drive larger workflows. The key idea is to convert a text search into a boolean condition, then use IF to branch results accordingly.
=IF(ISNUMBER(SEARCH("ship", A2)), "Yes", "No")This example uses SEARCH to locate the substring 'ship' inside A2 in a case-insensitive way. If found, SEARCH returns a number; ISNUMBER converts that to TRUE, letting IF return 'Yes'.
Basic tests with SEARCH and FIND
Two foundational patterns power most contains checks:
- SEARCH: a case-insensitive search that returns the position of the substring when found, or an error if not found.
- FIND: a case-sensitive search with identical behavior to SEARCH for the return value.
=IF(ISNUMBER(SEARCH("apple", A2)), "Match", "No match")=IF(ISNUMBER(FIND("apple", A2)), "Match (case-sensitive)", "No match")Notes:
- The ISNUMBER wrapper prevents #VALUE! errors when the substring isn't present.
- Use SEARCH when you want to ignore case; use FIND when you need case sensitivity.
Case sensitivity and pattern matching with REGEXMATCH
REGEXMATCH offers powerful substring checks with patterns. It can be case-insensitive or targeted to complex patterns with alternation and anchors.
=IF(REGEXMATCH(A2, "(?i)apple"), "Contains apple", "Does not contain")=IF(REGEXMATCH(A2, "cat|dog"), "Contains cat or dog", "None")Why REGEXMATCH here:
- You can describe flexible rules (e.g., contains one of many terms) in a single expression.
- The (?i) flag makes the match case-insensitive; omit it for exact-case checks.
- You can anchor patterns or use boundaries to refine results.
Practical examples: conditional formatting and data validation
Contains checks are not limited to plain formulas; you can leverage them in conditional formatting and data validation to highlight or constrain data automatically.
=REGEXMATCH(A2, "(?i)urgent")Set a conditional format rule with a custom formula using this pattern to colour rows that contain the word urgent, regardless of case. You can also use IF with REGEXMATCH inside data validation rules to permit or reject entries based on text presence.
Pros:
- Immediate visual feedback.
- Scales to entire columns with ARRAYFORMULA.
Cons:
- Overlapping rules can cause conflicting formatting; manage priority order carefully.
Multi-contains: OR logic and alternatives
When you need to detect multiple substrings, you can use regex alternation or multiple REGEXMATCH calls combined with OR.
=IF(REGEXMATCH(A2, "cat|dog"), "Contains cat or dog", "Other")=IF(OR(REGEXMATCH(A2, "cat"), REGEXMATCH(A2, "dog")), "Contains cat or dog", "Other")Tips:
- REGEXMATCH with cat|dog handles both substrings in one formula.
- OR with REGEXMATCH is functionally similar but can be easier to read for beginners.
Working with ranges: ARRAYFORMULA and FILTER
Applying contains logic across large ranges is common. Use ARRAYFORMULA to vectorize your contains tests and FILTER to extract matching rows.
=ARRAYFORMULA(IF(REGEXMATCH(A2:A, "(?i)apple"), "Yes", "No"))=FILTER(B2:B, REGEXMATCH(A2:A, "(?i)apple"))Performance note:
- ARRAYFORMULA avoids manual dragging; however, very large arrays can impact sheet performance. Pair with QUERY or FILTER when possible to limit processed rows.
Common pitfalls and performance considerations
Contains checks can fail if data has leading/trailing spaces or non-visible characters. Always trim and normalize data before testing.
=IF( LEN(TRIM(A2))=0, "empty", IF(REGEXMATCH(TRIM(A2), "(?i)apple"), "Yes", "No"))Another pitfall is relying on simple text checks for highly variable content; use REGEXMATCH with well-constructed patterns to reduce false negatives.
If you’re testing across a whole column, prefer ARRAYFORMULA with a narrow pattern or use FILTER/QUERY to minimize the number of evaluated rows.
Alternatives and best practices for contains logic
Sometimes it’s cleaner to push contains logic into higher-level functions like FILTER or QUERY instead of keeping a long IF formula in every row.
=FILTER(A2:A, REGEXMATCH(A2:A, "(?i)urgent|asap"))=QUERY(A1:B, "select B where A matches '(?i)urgent|asap'", 1)Best practices:
- Use REGEXMATCH for flexible matching with fewer formulas.
- Keep your patterns simple and well-documented.
- Combine with ARRAYFORMULA or FILTER to scale across data sets.
Steps
Estimated time: 20-40 minutes
- 1
Identify the text column
Locate the column that contains the strings you want to test for a contains condition. Ensure data is clean (trim spaces where needed) to avoid false negatives.
Tip: Tip: Start with a small sample (5–10 rows) to validate your pattern. - 2
Choose a test approach
Decide whether a simple case-insensitive contains (SEARCH) or a more flexible pattern (REGEXMATCH) best fits your data. For simple checks, use SEARCH with ISNUMBER; for complex patterns, REGEXMATCH is preferable.
Tip: Tip: Use (?i) inside your regex for case-insensitive matching. - 3
Create a helper formula
Insert a test formula in a helper column (e.g., B2) to verify matches before dragging or applying an array formula.
Tip: Tip: Start with a single-row test to confirm syntax and results. - 4
Scale to the full dataset
Replace the manual fill with ARRAYFORMULA or use FILTER/QUERY to apply the contains logic across large ranges efficiently.
Tip: Tip: Validate performance for datasets larger than a few thousand rows. - 5
Validate and iterate
Check edge cases: blanks, extra spaces, punctuation, and mixed case. Adjust patterns or wrap with TRIM as needed.
Tip: Tip: Document your chosen pattern for future readers of the sheet.
Prerequisites
Required
- Required
- Basic knowledge of IF, SEARCH, FIND, and REGEXMATCHRequired
Optional
- Familiarity with ARRAYFORMULA and FILTEROptional
- Ability to edit conditional formatting rulesOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy selected cell or formula | Ctrl+C |
| PastePaste into target cell or field | Ctrl+V |
| FindOpen find dialog for the sheet | Ctrl+F |
| BoldToggle bold in the active cell or selection | Ctrl+B |
| UnderlineToggle underline in the active cell or selection | Ctrl+U |
FAQ
What is the difference between FIND and SEARCH when testing contains in Google Sheets?
FIND is case-sensitive, while SEARCH ignores case differences. Choose based on whether you need exact case matching. For more flexibility, REGEXMATCH can handle complex patterns beyond basic contains.
FIND is case-sensitive; SEARCH isn't. For flexible patterns, REGEXMATCH is often the best choice.
Can I apply contains logic to an entire column without copying formulas?
Yes. Use ARRAYFORMULA to propagate a contains check down the entire column, or use FILTER to return only matching rows. This avoids manual dragging and keeps formulas scalable.
Yes—use ARRAYFORMULA or FILTER to work across columns or rows without dragging.
How do I check for multiple substrings at once?
Use REGEXMATCH with an alternation like cat|dog, or combine several REGEXMATCH calls with OR. This simplifies patterns when you need to detect any of several terms.
REGEXMATCH with cat|dog makes multi-substring checks straightforward.
Why do I see #VALUE! with FIND/SEARCH?
If the substring isn't found, FIND/SEARCH return an error. Wrap the test with ISNUMBER or switch to REGEXMATCH to avoid errors and produce boolean results.
Not found equals an error with FIND/SEARCH; use ISNUMBER or REGEXMATCH instead.
Contains in conditional formatting?
Yes. Create a conditional formatting rule using a custom formula with REGEXMATCH, such as =REGEXMATCH(A2, "(?i)urgent"), to highlight cells containing the target term.
You can highlight containsText with a REGEXMATCH rule in conditional formatting.
Large data performance?
Contains checks can become slow on very large datasets. Optimize by limiting the tested range, using ARRAYFORMULA carefully, and preferring FILTER/QUERY when possible.
Yes—watch performance with big data and optimize with targeted ranges.
The Essentials
- Use REGEXMATCH for flexible, case-insensitive contains checks
- Combine IF with SEARCH or FIND for simple contains tests
- Trim whitespace to avoid false negatives
- Apply across ranges with ARRAYFORMULA or use FILTER/QUERY for performance