To test whether a cell contains a particular string in Excel, use =ISNUMBER(SEARCH("apple",A2)), replacing “apple” with the text to find and A2 with the cell to check. It returns TRUE when the text appears anywhere in the cell and FALSE when it does not; SEARCH ignores letter case.
How the contains test works
Excel does not have a function literally named CONTAINS. Instead, combine SEARCH with ISNUMBER. SEARCH looks for one text string inside another and returns the position where the match starts. If it cannot find the text, it returns an error. ISNUMBER turns those outcomes into a convenient logical result: a number becomes TRUE, and an error becomes FALSE.
For example, with a product description in A2 containing “Fresh apple juice,” the formula above returns TRUE because “apple” begins at character 7. If A2 contains “Fresh pear juice,” it returns FALSE. The check works even when the target is at the start or end of the cell, and it does not require the whole cell to match.
In Microsoft 365 for Windows, enter the formula in another cell, such as B2, and press Enter. If you have several rows, fill the formula down using the fill handle at the lower-right corner of B2. The row reference changes automatically, so the next row checks A3. Excel for Mac uses the same formula and function behavior; the formula itself does not differ.
Choose SEARCH or FIND for case sensitivity
SEARCH is the usual choice for a contains test because it is not case-sensitive. It treats “apple,” “Apple,” and “APPLE” as matches for the same target. That is useful for customer notes, product descriptions, and other text where capitalization is inconsistent.
FIND performs a similar position search but is case-sensitive. With text to check in A2, this formula returns TRUE only if the exact capitalization “Apple” occurs in the cell:
With the target text “Apple” and the string in A2, use =ISNUMBER(FIND("Apple",A2)). For a cell containing “Green Apple,” the result is TRUE; for “green apple,” it is FALSE.
Choose the function based on what counts as a match, not on which one looks shorter. SEARCH is appropriate for ordinary text searches; FIND is useful when capitalization carries meaning, such as a code or a naming convention. Both return a position rather than the matched text itself, which is why ISNUMBER is commonly wrapped around them.
Return a label instead of TRUE or FALSE
A logical result is useful in a helper column, but a report may need words such as “Found” and “Not found.” Wrap the test in IF to choose what Excel displays. With the description in A2, the following formula returns “Found” when it contains “urgent” and “Not found” otherwise:
With the text to inspect in A2, use =IF(ISNUMBER(SEARCH("urgent",A2)),"Found","Not found"). If A2 says “Urgent: call customer,” the result is Found; if it says “Routine update,” the result is Not found.
The labels can be changed to match your workflow, such as “Review” and “OK,” or “1” and “0.” Remember that quoted labels are text values. If you plan to count or filter the results, a consistent label or a TRUE/FALSE result is often easier to work with than several variations such as “Found,” “Yes,” and “Match.”
Search for text stored in another cell
You do not have to type the search term directly into the formula. Keeping it in a cell makes the check easier to reuse or change. Suppose A2 contains the description and B2 contains the search term. The following checks whether the text in B2 appears in A2:
With the string in A2 and the term in B2, use =ISNUMBER(SEARCH(B2,A2)). If A2 is “Blue cotton shirt” and B2 is “cotton,” the result is TRUE.
There is one important edge case: a blank search term is treated as a match by SEARCH, so a blank B2 can make the test return TRUE. If the search-term cell might be empty, guard against that. With A2 as the text and B2 as the term, use =AND(B2<>"",ISNUMBER(SEARCH(B2,A2))). The result is FALSE when B2 is blank, and otherwise it tests for the term normally. When copying this setup down a list, make sure the search-term reference is appropriate: use a fixed reference such as $B$2 if every row should use one shared term.
Check for one of several terms
To find a cell that contains at least one of several words, combine individual SEARCH tests with OR. Each search must be converted with ISNUMBER so that OR receives TRUE or FALSE values. With a customer note in A2, this formula checks for either “urgent” or “overdue”:
With the note in A2, use =OR(ISNUMBER(SEARCH("urgent",A2)),ISNUMBER(SEARCH("overdue",A2))). A2 containing “The order is overdue” returns TRUE; “Please review next week” returns FALSE.
For a cell that must contain both terms, use AND instead. With the same note in A2, =AND(ISNUMBER(SEARCH("apple",A2)),ISNUMBER(SEARCH("fresh",A2))) returns TRUE only if both “apple” and “fresh” appear somewhere in that cell. The terms can occur in either order and do not have to be next to each other.
These formulas are practical when the list of terms is short. If the terms change frequently or there are many of them, keep the terms in a worksheet range and consider a more structured approach, such as helper columns or a formula designed around that list. A long formula with many manually typed searches can be difficult to maintain and easy to edit incorrectly.
Use wildcards with COUNTIF
COUNTIF offers another way to test for text within a cell. Its asterisk wildcard represents any number of characters, including none. With the text in A2, =COUNTIF(A2,"*apple*")>0 returns TRUE if the cell contains “apple” anywhere. The asterisks allow any characters before or after the target.
COUNTIF is not case-sensitive, so it suits simple substring checks. Unlike SEARCH, it is a counting function: the comparison with zero turns the count into TRUE or FALSE. This approach can be familiar if you already use criteria such as “begins with” or “ends with” in COUNTIF formulas.
Wildcards have special meanings in criteria. The asterisk matches any sequence of characters, and the question mark matches one character. To search for a literal asterisk or question mark, prefix it with a tilde in the criterion. For more control, especially when the target itself may contain wildcard characters, SEARCH is often clearer than COUNTIF.
Find whole words rather than a substring
A standard contains test checks for a sequence of characters, not a complete word. Searching for “cat” will also match “catalog” and “education.” This is not a formula error; it is what substring matching means. If the distinction matters, decide how your data separates words before choosing a method.
For simple text with spaces between words, padding the cell and the search term with spaces can approximate a whole-word check. With the text in A2, this formula looks for “cat” surrounded by spaces, including when it appears at the beginning or end of the cell:
With A2 containing ordinary space-separated words, use =ISNUMBER(SEARCH(" cat "," "&A2&" ")). It matches “a cat here” but not “catalog.” However, it will not treat commas, periods, hyphens, or other punctuation as word boundaries. For example, “cat,” has a comma after the word and may not match this space-only pattern.
There is no single basic SEARCH formula that reliably defines every possible word boundary for every language and punctuation style. If exact word matching is important, standardize the source text or use a more deliberate approach that accounts for your separators. Test the formula against examples that include punctuation, multiple spaces, and text at the start or end of a cell.
Highlight cells that contain text
If you want to spot matching cells rather than display a helper result, use conditional formatting. Select the range to format, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter a formula and choose a format, such as a fill color. On Excel for Mac, the conditional-formatting commands are also on the Home tab, though the dialog layout can differ.
For a selected range A2:A100, enter =ISNUMBER(SEARCH("late",A2)) as the rule formula. A2 is the first cell in the selected range, so Excel evaluates the rule against each row in turn. Choose the format and confirm the rule. Cells containing “late,” including “Late delivery,” are highlighted because SEARCH ignores case.
Check the “Applies to” range if the formatting appears on the wrong rows. The reference A2 should normally be relative by row so it adjusts for each cell, while any fixed term cell may need dollar signs. Conditional formatting changes appearance, not the stored cell value, so it is not a substitute for a result column when you need to filter or calculate from the matches.
Common errors and troubleshooting
The most common problem is seeing #VALUE! after using SEARCH or FIND directly. That error means the requested text was not found. Wrapping the search in ISNUMBER converts the not-found case into FALSE, which is generally easier to use in later formulas.
Other issues usually come from the input or the match rule rather than Excel malfunctioning. Check these points when a result surprises you:
- Unexpected TRUE for a blank term: SEARCH treats an empty search string as a match. Add a blank-cell check when the target comes from another cell.
- Case mismatch: SEARCH ignores case, while FIND distinguishes uppercase and lowercase. Use the one that matches your requirement.
- Partial-word match: A contains test finds characters inside longer words. It does not automatically recognize whole words.
- Wrong reference: When filling a formula down, confirm that the checked-cell reference moves and that any shared term reference is locked if needed.
- Text that looks identical but differs: Imported data can include extra spaces or nonprinting characters. Inspect or clean the source if a visible match is not being detected as expected.
Also distinguish a text search from a test for a cell containing any text at all. SEARCH checks for a particular string. If you instead need to know whether A2 contains text of any kind, a different test is appropriate: =ISTEXT(A2). That returns TRUE for text values, but not for numbers stored as numbers, blank cells, or errors. A number that merely looks like text because of its formatting is still a numeric value to Excel.
Frequently Asked Questions
Is SEARCH case-sensitive in Excel?
No. SEARCH ignores capitalization. Use FIND instead when uppercase and lowercase must be distinguished, and wrap either function in ISNUMBER for a TRUE/FALSE result.
How do I check whether a cell contains any text?
If you mean whether the cell value is text rather than whether it includes a particular word, use =ISTEXT(A2) with the value in A2. It does not check for a specific substring.
How do I return Yes or No when text is found?
With the text to check in A2, use =IF(ISNUMBER(SEARCH("word",A2)),"Yes","No"), replacing “word” with the term you need. The formula returns Yes for a match and No otherwise.
Can Excel search for text in another cell?
Yes. If A2 contains the string and B2 contains the term, =ISNUMBER(SEARCH(B2,A2)) tests the value in B2. Add a check for B2 being blank if an empty term should not count as a match.
Why does searching for a word match a longer word?
SEARCH looks for characters anywhere in the cell, so “cat” matches “catalog.” It does not understand word boundaries. A space-padding workaround can work for simple space-separated text, but punctuation requires additional handling.
Which method should you use?
For most everyday checks, use =ISNUMBER(SEARCH("text",A2)): it is readable, case-insensitive, and returns a direct logical result. Choose FIND when capitalization matters, IF when you need a label, OR or AND when you have several terms, and COUNTIF when wildcard criteria are convenient. Use conditional formatting when the goal is visual scanning, and ISTEXT only when the question is whether the cell value is text at all.
Before relying on a match for reporting or cleanup, test a few known examples: a match at the start, one in the middle, one at the end, a capitalization difference, and a non-match that contains similar letters. Those checks make the intended rule clear and help reveal whether you need substring matching, case sensitivity, or whole-word logic.
Related
- Data Validation Process: Steps, Checks, and Best Practices
- Data Validation Named Range in Excel: Setup and Fixes
- Data Validation Reference Table in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Difference Between Two Numbers: Formula GuideTo find the signed difference between two numbers in Excel, subtract one cell from the other, such as…
- Excel Sum Based on Criteria with SUMIF and SUMIFSIf categories are in A2:A7 and amounts are in B2:B7, use =SUMIF(A2:A7,"East",B2:B7) to add the amounts for East;…
- Xlookup vs vlookup?XLOOKUP defaults to exact matches and can return values from either side. See where VLOOKUP still fits and…
- Excel Shortcut to Pull Down a Formula: Fill Down FastIn Excel for Microsoft 365 on Windows, select the formula cell and the cells below where you want…
- Filter Cells by Color in Excel: Steps and TipsTo filter cells by color in Excel for Microsoft 365 on Windows, select your data, choose Data >…
- Insert a Row in Excel With a Formula: Real OptionsA formula cannot insert a physical worksheet row or move existing cells. To show a blank row inside…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.