Google Sheets Text Contains: A Practical Guide
Learn to detect substrings in Google Sheets using FIND, SEARCH, and REGEXMATCH with practical formulas, examples, and common pitfalls.
According to How To Sheets, you can detect substrings in a cell with FIND or SEARCH, or with REGEXMATCH for patterns. A simple test is =ISNUMBER(SEARCH("term", A2)), which returns TRUE when the term appears. For more complex patterns, REGEXMATCH(A2, "pattern") yields TRUE or FALSE. You can extend tests with ARRAYFORMULA for column-wide checks.
What google sheets text contains means in practice
The concept of text containment in Google Sheets centers on detecting whether a cell's content includes a given substring. This is essential for data validation, cleaning, and dynamic filtering. In this guide, the keyword google sheets text contains will appear frequently as you learn practical formulas, patterns, and edge cases. Below you'll find several approaches, starting with simple searches and moving to robust pattern matching.
=ISNUMBER(SEARCH("sales", A2))This formula returns TRUE when the substring is found in A2. Unlike FIND, SEARCH is case-insensitive, which is convenient for quick checks across mixed-case text. If you want a strict yes/no, wrap the result in IF:
=IF(ISNUMBER(SEARCH("sales", A2)), TRUE, FALSE)Why this matters: being able to test containment enables conditional formatting, filtering, and automated data auditing across large datasets. As you progress, you’ll replace hard-coded literals with cell references and leverage ARRAYFORMULA for column-wide checks.
=ARRAYFORMULA(ISNUMBER(SEARCH("sales", A2:A)))Basic Approach: FIND and SEARCH
For straightforward substring checks, FIND and SEARCH are the quickest tools. FIND is case-sensitive, while SEARCH ignores case. This distinction matters when your data includes mixed-case entries. The following examples illustrate typical usage and how to handle missing results without breaking your formulas.
=ISNUMBER(FIND("Sales", A2))This returns TRUE only if A2 contains the exact case "Sales". To avoid #VALUE! errors when the substring isn't present, use IFERROR:
=IFERROR(ISNUMBER(FIND("Sales", A2)), FALSE)For case-insensitive matching across a whole column, you can combine with ARRAYFORMULA:
=ARRAYFORMULA(ISNUMBER(SEARCH("sales", A2:A)))Pattern Matching with REGEXMATCH
REGEXMATCH offers flexible, pattern-based containment. It can handle multiple terms, boundaries, and optional groups. Use it when you need to detect one of several substrings, or when the text structure is predictable. Note that REGEXMATCH returns TRUE or FALSE and requires a valid regular expression.
=REGEXMATCH(A2, "(sales|budget|invoice)")To apply across a range, wrap with ARRAYFORMULA:
=ARRAYFORMULA(REGEXMATCH(A2:A, "(sales|budget|invoice)") )If you want a case-insensitive match, you can add (?i):
=REGEXMATCH(A2, "(?i)sales|budget|invoice")Extracting or Counting Matches
Beyond boolean tests, you can extract the matched text with REGEXEXTRACT or count occurrences with a small formula. REGEXEXTRACT pulls the first matching substring, which is useful for parsing semi-structured data.
=REGEXEXTRACT(A2, "(sales|budget|invoice)")Counting occurrences of a term within a cell can be done via length tricks with SUBSTITUTE:
=IFERROR((LEN(LOWER(A2)) - LEN(SUBSTITUTE(LOWER(A2), "sales", ""))) / LEN("sales"), 0)For multiple cells, use ARRAYFORMULA:
=ARRAYFORMULA(REGEXEXTRACT(A2:A, "(sales|budget|invoice)") )Practical workflow: testing on real data
Let's test with a small sample and show how to audit results. Suppose you have a column A with product descriptions. We'll create a helper column B that flags whether the description contains any of several keywords and another column C that extracts the matched keyword.
=REGEXMATCH(A2, "(?i)\\b(sales|budget|invoice)\\b")=REGEXEXTRACT(A2, "(?i)\\b(sales|budget|invoice)\\b")To apply to entire column and keep things fast, use ARRAYFORMULA in the header row:
=ARRAYFORMULA(IF(A2:A="",, REGEXMATCH(A2:A, "(?i)\\b(sales|budget|invoice)\\b")))Common pitfalls and debugging tips
Containment formulas can fail for various reasons: case sensitivity, escaping special characters in regex patterns, or trailing spaces. Start by testing simple checks in a single cell, then expand using ARRAYFORMULA. Use TRIM to remove stray spaces and LOWER/UPPER to standardize case. Always handle errors with IFERROR to avoid noisy results.
=IFERROR(SEARCH("sales", TRIM(A2)), 0)If you need a literal regex, remember to escape backslashes in quotes:
=REGEXMATCH(A2, "\\bSales\\b")Performance tips and best practices
When working with large sheets, avoid heavy regex across entire columns; pre-filter data with simpler criteria, or run expensive tests sparingly using FILTER or QUERY. Cache results in a helper column and reference it in downstream formulas. If you must scan many cells, prefer REGEXMATCH with direct pattern rather than multiple FIND calls.
=ARRAYFORMULA(REGEXMATCH(A2:A, "(?i)\\b(sales|budget|invoice)\\b"))Good optimization practice includes converting boolean results to text with IF to produce friendly labels, e.g., "Found"/"Not Found".
Appendix: quick reference formulas
- Case-sensitive contains (FIND):
=ISNUMBER(FIND("Sales", A2))- Case-insensitive contains (SEARCH):
=ISNUMBER(SEARCH("sales", A2))- Pattern match with REGEXMATCH:
=REGEXMATCH(A2, "(?i)\b(sales|budget)\b")- Extract first match:
=REGEXEXTRACT(A2, "(?i)\b(sales|budget|invoice)\b")Steps
Estimated time: 15-25 minutes
- 1
Plan your pattern
Identify the target column and the keywords or patterns you want to detect. Decide whether you need a strict contains check or a flexible pattern. Prepare a small sample dataset to validate your formulas before applying to the whole sheet.
Tip: Start with a single cell test before scaling to a column. - 2
Choose the right test
Use FIND for exact case-sensitive matches, or SEARCH for case-insensitive checks. For multiple terms, REGEXMATCH is often easier and more scalable.
Tip: Remember: FIND is case-sensitive, while SEARCH is not. - 3
Handle errors gracefully
Wrap tests in IFERROR to avoid #VALUE! or #N/A errors when a substring isn’t found. This keeps downstream formulas clean.
Tip: IFERROR is your friend when dealing with optional matches. - 4
Scale to a range
Apply the test across a range with ARRAYFORMULA to produce a TRUE/FALSE array for an entire column.
Tip: Use REGEXMATCH with ARRAYFORMULA for patterns across columns. - 5
Extract or count matches
If you need the actual matched text, use REGEXEXTRACT; for counts, combine LEN and SUBSTITUTE with lowercased text to count occurrences.
Tip: REGEXEXTRACT returns the first match; for multiple matches, consider iterative approaches. - 6
Validate with real data
Test formulas on realistic data, check edge cases (empty cells, special characters), and refine patterns accordingly.
Tip: Document your patterns so others can reuse them.
Prerequisites
Required
- Required
- Basic knowledge of Google Sheets formulas (FIND, SEARCH, REGEXMATCH)Required
Optional
- Familiarity with relative vs absolute references in formulasOptional
- A sample dataset to practice onOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| Copy cellCopy the selected cell | Ctrl+C |
| Paste to multiple cellsPaste values only or use normal paste as needed | Ctrl+⇧+V |
| Find in sheetOpen Find dialog to search within the sheet | Ctrl+F |
| Fill downFill formula down a column | Ctrl+D |
| Open Find and ReplaceSearch and replace text in range | Ctrl+H |
| Enter formulaEnter/accept formula in a cell | ↵ |
FAQ
What is the difference between FIND and SEARCH in Google Sheets?
FIND is case-sensitive, returning an error if the substring isn’t found. SEARCH ignores case, returning a number when found or an error otherwise. Use the one that matches your needs and wrap in IFERROR to handle misses.
FIND is case-sensitive, while SEARCH is not. Use IFERROR to handle missing substrings.
Can REGEXMATCH detect multiple substrings at once?
Yes. Use a pattern with alternation, like "sales|budget|invoice", to detect any of several terms. REGEXMATCH returns TRUE or FALSE for each cell.
Yes—REGEXMATCH can check for multiple terms using a pattern with | as an OR operator.
Is there a way to extract the actual matched text, not just a boolean?
Yes. REGEXEXTRACT returns the first matching substring. You can combine it with REGEXMATCH to validate and extract in one step.
You can pull out the matched text with REGEXEXTRACT.
How do I apply contains logic to an entire column efficiently?
Use ARRAYFORMULA with REGEXMATCH or SEARCH to generate a boolean array for the entire column. This avoids copying formulas down many rows.
Apply it once with ARRAYFORMULA to cover the whole column.
Why do I sometimes get #VALUE! with FIND or SEARCH?
This occurs when the substring isn’t found. Use IFERROR to provide a default value or FALSE. Trim inputs to avoid stray spaces.
If not found, you’ll see an error—use IFERROR to handle it gracefully.
Does Google Sheets have a dedicated 'text contains' function?
No. Most use FIND/SEARCH or REGEXMATCH to implement a contains check with more flexibility and pattern support.
There isn't a separate contains function; use FIND/SEARCH or REGEXMATCH instead.
The Essentials
- Test contains with FIND/SEARCH
- Prefer REGEXMATCH for complex patterns
- Wrap tests with IFERROR to avoid errors
- Use ARRAYFORMULA for column-wide checks
- REGEXEXTRACT can fetch matched text