Excel contains: Practical substring checks in Excel formulas
Learn how to test substrings in Excel without a dedicated CONTAINS function, using SEARCH, FIND, and COUNTIF with wildcards. This guide covers case sensitivity, array formulas, and practical examples for Excel 365 and Excel Online.
Excel does not have a dedicated CONTAINS function. To test whether a cell contains a substring, use SEARCH or FIND wrapped in ISNUMBER, or COUNTIF with wildcards. For case-insensitive checks, use SEARCH; for exact case, use FIND. Combine these with IF to return booleans or messages, applicable in both desktop and online Excel.
Understanding the contains concept in Excel
When you work with datasets, you often need to know if a cell or a range contains a specific substring. There is no built-in CONTAINS function in Excel, but you can achieve the same result with a few dependable functions. According to XLS Library, this approach is essential for data validation, cleaning, and conditional formatting. In practice, you typically combine a search function with an error handler to obtain a clean boolean or a descriptive label. The following examples demonstrate core patterns, then we extend to multi-cell ranges and dynamic arrays.
=ISNUMBER(SEARCH("report", A2))This formula returns TRUE if the substring 'report' appears anywhere in A2, ignoring the case. For an exact match, you could use FIND instead of SEARCH:
=ISNUMBER(FIND("report", A2))Steps
Estimated time: 45-60 minutes
- 1
Identify the target column
Decide which column (or range) you want to evaluate for the presence of a substring. This minimizes unnecessary calculations and clarifies where you’ll place the resulting boolean or message.
Tip: Create a separate column for results to keep data clean. - 2
Choose a pattern (case-insensitive vs. sensitive)
If you need a case-insensitive test, prefer SEARCH; for case-sensitive tests, use FIND. This choice determines your formula behavior and results.
Tip: Remember: FIND is strict about case; SEARCH ignores it. - 3
Implement a basic contains check
Write a simple formula to verify presence and return a boolean or label. Use ISNUMBER to convert the search result to TRUE/FALSE.
Tip: Wrap with IF for readable outputs like Yes/No. - 4
Handle errors gracefully
If the substring might be missing, use IFERROR to avoid #VALUE! errors and return a clean result like FALSE.
Tip: IFERROR(ISNUMBER(SEARCH(...)), FALSE) is a common pattern.
Prerequisites
Required
- Required
- Basic knowledge of string functions (FIND, SEARCH, LEFT, RIGHT)Required
Optional
- Optional: sample workbook with text dataOptional
- Familiarity with array formulas or dynamic arraysOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy formulas or text in cells | Ctrl+C |
| PastePaste into target cells | Ctrl+V |
| Fill DownFill formula down a column | Ctrl+D |
People Also Ask
Does Excel have a built-in CONTAINS function?
No. Excel does not include a dedicated CONTAINS function. Use SEARCH or FIND wrapped in ISNUMBER, or COUNTIF with wildcards to test for substrings. These patterns work in both desktop and online Excel.
No built-in CONTAINS in Excel; use search-based patterns like ISNUMBER(SEARCH(...)) or COUNTIF with wildcards.
How do I ignore case when checking if a cell contains text?
For case-insensitive checks, prefer SEARCH over FIND since SEARCH ignores case differences. Wrap the result in ISNUMBER to produce a boolean, or use IF for readable output.
Use SEARCH for case-insensitive checks, wrapped in ISNUMBER for a boolean result.
Can I check multiple substrings at once within a range?
Yes. You can combine multiple ISNUMBER(SEARCH(...)) checks with OR, or use LET to define multiple terms. Dynamic arrays or FILTER can help apply the condition to entire columns.
Yes—combine multiple searches with OR or use LET for multiple terms and then apply to a range.
How can I filter rows where a column contains a substring?
Use FILTER with ISNUMBER(SEARCH(term, range)). This returns rows where the substring appears in the specified column.
Use FILTER together with ISNUMBER(SEARCH(...)) to extract matching rows.
The Essentials
- Use ISNUMBER(SEARCH(...)) for contains checks
- FIND is case-sensitive; SEARCH is not
- COUNTIF with wildcards handles whole ranges
- Use LET/SUM with ISNUMBER(SEARCH(...)) for multiple substrings
- Dynamic arrays simplify multi-row contains checks