To check whether a cell contains a particular word or phrase, use =ISNUMBER(SEARCH("text",A2)), replacing “text” with the text you want to find. If you instead want to know whether a cell’s value is text at all, use =ISTEXT(A2); these formulas answer different questions.
What “contains text” can mean in Excel
Excel does not have a built-in function named CONTAINS. The phrase “contains text” usually refers to one of two tests: finding a sequence of characters somewhere inside a cell, or checking whether the cell’s value is stored as text. Choosing the right test matters because a cell can contain text without containing the particular word you are looking for.
For instance, a cell containing “Blue bicycle” contains the word “bicycle” and is also a text value. A cell containing the number 42 is not a text value, even though you can display it with text such as a label or a custom number format. A cell containing the text “42,” entered or imported as text, is a text value. Excel can distinguish these cases.
The most common search formula combines SEARCH with ISNUMBER. SEARCH looks for a character sequence and returns its position when it finds it; ISNUMBER turns that position into TRUE. When SEARCH cannot find the requested text, it returns an error, which ISNUMBER evaluates as FALSE. This makes the combination convenient for IF formulas, filters, and conditional formatting.
Use SEARCH to find a word or phrase
Assume A2 contains the text “Blue bicycle” and you want to find “bicycle” anywhere in it. With the text being searched in A2, the formula is =ISNUMBER(SEARCH("bicycle",A2)). It returns TRUE because “bicycle” starts at character six. If A2 instead contains “Blue scooter,” it returns FALSE.
SEARCH is not case-sensitive: searching for “BICYCLE” also finds “bicycle.” The search can match a fragment, not just a whole word. Searching for “cycle” will match “bicycle,” and searching for “cat” can match “catalog.” If you need to distinguish whole words from fragments, a simple SEARCH formula may be too broad; the right solution depends on the text format and what counts as a word in your data.
You can put the search term in a cell instead of typing it into the formula. Suppose A2 contains the item description and B1 contains the term to look for. With those cell locations, use =ISNUMBER(SEARCH(B1,A2)). Changing B1 then changes the test without editing the formula. This is useful for a reusable lookup field or a report where someone can enter a term.
Use ISTEXT to test the value type
ISTEXT checks whether a value is text; it does not search inside the value for a particular word. With the value to test in A2, enter =ISTEXT(A2). It returns TRUE for ordinary text such as “Pending” and for a number stored as text such as “0042.” It returns FALSE for the numeric value 42 and for a genuinely empty cell.
This distinction is useful when imported data behaves unexpectedly. A quantity that looks like 42 may be stored as text, which can affect calculations, sorting, and matching. ISTEXT can identify those values, but it does not convert them. Conversion may require correcting the import, using Excel’s conversion tools, or applying a suitable formula based on the data.
A formula that returns an empty string, such as one that displays nothing, is still returning text. Therefore ISTEXT can return TRUE for a cell that looks blank but contains a formula returning an empty string. That is different from a cell that has never contained a value. If your workflow must distinguish visually blank cells from truly empty cells, test the actual situation rather than relying on appearance alone.
Choose SEARCH or FIND for case sensitivity
SEARCH ignores capitalization, which is generally helpful when matching names, descriptions, or notes. FIND performs a similar position search but is case-sensitive. If A2 contains “Northwind,” then searching for lowercase “north” with SEARCH finds a match, while FIND does not.
Assume A2 contains the text to inspect and you want the match to respect capitalization. Use =ISNUMBER(FIND("North",A2)). It returns TRUE for “Northwind” and FALSE for “northwind.” FIND and SEARCH both return an error when they do not find the requested text, so putting either inside ISNUMBER gives a straightforward TRUE-or-FALSE result.
SEARCH and FIND also recognize wildcard characters in the search text. An asterisk (*) can stand for any number of characters, and a question mark (?) can stand for one character. Use a tilde (~) before a wildcard when you want to search for the literal character rather than use its wildcard meaning. This matters when searching data that contains asterisks or question marks as actual characters.
Return a label instead of TRUE or FALSE
TRUE and FALSE are useful for logic, but a report may be clearer with words such as “Found” and “Not found.” Assume A2 contains the text to inspect and you want to check for “invoice.” Use =IF(ISNUMBER(SEARCH("invoice",A2)),"Found","Not found"). For “May invoice,” the result is “Found”; for “May receipt,” it is “Not found.”
You can also use the Boolean test to count matching cells. Suppose descriptions are in A2:A20 and D1 contains the search term. With that layout, enter =COUNTIF(A2:A20,"*"&D1&"*"). If D1 contains “bike,” the formula counts cells that contain “bike” anywhere within their contents. The asterisks act as wildcards around the term. COUNTIF matching is not case-sensitive, and wildcard characters typed into D1 are treated as wildcards too, so a term containing * or ? may need special handling.
For a single row, the search formula can also serve as a test in a larger formula. For example, you can use it as the logical test in IF, or combine it with other tests using AND or OR. Build and verify the basic search first; then add extra conditions so it is easier to see which part of a more complex formula is responsible for a result.
Highlight cells that contain a term
To color matching cells without adding a helper column, use conditional formatting. Select the range you want formatted, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Assume the selected range is A2:A100 and you want to highlight cells containing “urgent.” Enter =ISNUMBER(SEARCH("urgent",A2)) as the rule formula, choose a format, and confirm the rule.
The reference A2 is relative: Excel adjusts it for each row in the selected range. If you select a different range, write the formula using the top-left cell of that selection. A mistaken starting reference can make Excel evaluate the wrong row, so check a few highlighted and unhighlighted cells after creating the rule. The same general approach works with a cell containing the search term, such as B1, if the rule formula refers to that cell.
Handle blanks, errors, and unexpected matches
An empty search term can produce a match rather than a useful “not found” result. If a user enters the search term in B1 and a blank B1 should produce no displayed result, assume the text to inspect is in A2 and use =IF(B1="","",ISNUMBER(SEARCH(B1,A2))). This returns an empty string when B1 is empty and otherwise returns TRUE or FALSE. Remember that the empty string is a text result, even though the cell appears blank.
If A2 may contain an error value from another formula, SEARCH can pass that error through, and the overall test may show an error instead of TRUE or FALSE. Do not hide errors automatically unless you know what they mean: an error could point to a broken reference or bad source data. If the intended behavior is to show FALSE whenever the search cannot be completed, and the search term is in B1 while the text is in A2, you can use =IFERROR(ISNUMBER(SEARCH(B1,A2)),FALSE).
Unexpected matches often come from searching for a short fragment. A search for “art,” for example, can match “artist” as well as a standalone “art.” Consider using a more distinctive phrase, standardizing the source data, or using a method designed for whole-word matching if false positives matter. Also check for leading or trailing spaces in the search term; a space is a character, so it affects what SEARCH can find.
Text that looks numeric is another common source of confusion. SEARCH can find digits in text or in a number converted for comparison, while ISTEXT reports the underlying value type. Neither test tells you whether a numeric-looking string is a valid number for a particular task. Confirm the data type and the intended match separately when cleaning imported records.
Which method should you use?
Use the method that matches the question rather than choosing a formula based only on the word “text.” These quick distinctions cover most worksheet needs:
- Use ISNUMBER with SEARCH to test for a case-insensitive substring in a cell.
- Use ISNUMBER with FIND when the match must be case-sensitive.
- Use ISTEXT to test whether a cell’s value is text, not whether it contains a chosen word.
- Use IF around a search test to return a readable label or manage a blank search input.
- Use COUNTIF with wildcard characters to count cells containing a term.
- Use a conditional-formatting rule with SEARCH to highlight matching cells visually.
For Microsoft Excel for Microsoft 365 on Windows, these functions and the conditional-formatting route described above are available without an add-in. Excel for Mac has the same core worksheet functions; ribbon labels or dialog layout can vary slightly by release. Excel for the web also supports these standard functions, though the available interface and formatting controls may differ from the desktop app.
Frequently Asked Questions
What is the Excel formula for contains text?
To find a particular substring, use ISNUMBER with SEARCH, such as =ISNUMBER(SEARCH("apple",A2)) when A2 contains the text to check. Replace “apple” with the term you need.
How do I check whether a cell contains any text?
Use =ISTEXT(A2) when the value is in A2. This checks the value type; it does not look for a particular word. A formula returning an empty string is text even if the cell looks blank.
How do I search without case sensitivity?
Use SEARCH, which ignores capitalization, inside ISNUMBER. For example, a search for “urgent” can find “URGENT” in the same cell.
How do I make an Excel search case-sensitive?
Use FIND instead of SEARCH, with ISNUMBER around it. FIND distinguishes uppercase and lowercase letters.
Why does my contains formula return FALSE?
Check that the search term is present exactly as entered, including spaces, and that you are searching the intended cell. Also check whether you expected a whole-word match or a fragment match: SEARCH looks for a sequence of characters and can match inside a longer word.
Can I highlight cells that contain a word?
Yes. Create a conditional-formatting rule using a formula such as =ISNUMBER(SEARCH("urgent",A2)), adjusting A2 to the first cell in the selected range. Choose the format to apply when the formula evaluates to TRUE.
Related
- Power Query Replace Text: Steps, Options, and Examples
- Conditional Formatting: Use More Than One Rule in Excel
- table() in RStudio: How It Counts and Displays Data
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- VLOOKUP in Excel: How to Apply It and Avoid ErrorsTo apply VLOOKUP in Excel, enter =VLOOKUP(E2,A2:C6,3,FALSE) in a blank cell when the value to find is in…
- Excel MIN Without Blanks: What the Formula IgnoresIf your values are in A2:A10, use =MIN(A2:A10) to find the smallest number while ignoring blank cells. Excel’s…
- Excel Table Query: How to Filter, Transform, and Refresh DataIn Excel for Microsoft 365 on Windows, the usual way to query an Excel table is to select…
- DAX Formula to Concatenate Text in Power BIIn Power BI, use the DAX & operator to join text: Customers[FirstName] & " " & Customers[LastName]. Use…
- Excel Column Number to Letter: Formula and ExamplesTo convert an Excel column number to its letter name, enter a number such as 28 in A2…
- Excel SUM and SUBTOTAL: Formulas, Filters, and ExamplesTo add a range in Excel, use =SUM(B2:B10). To add only the rows currently shown after filtering, use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.