To check whether a cell contains a particular string of text, use =ISNUMBER(SEARCH("apple",A2)). With the word apple anywhere in A2, Excel returns TRUE; if it is absent, the formula returns FALSE. SEARCH ignores capitalization, so use FIND instead when uppercase and lowercase must be distinguished.
What “Contains” Means in Excel
In this question, a string means text: letters, words, numbers stored as text, or a combination of characters. “Contains” usually means that the search text appears anywhere inside another cell’s value, not that the cell equals the search text. For example, a cell containing Green apples contains apple, even though the complete cell value is longer.
Excel does not have a worksheet function literally named CONTAINS. Instead, you can search for a substring and test whether Excel found a position for it. SEARCH and FIND return the position where the match begins. ISNUMBER converts that result into the convenient TRUE or FALSE answer used in the opening formula.
This is different from asking whether a cell is text at all. A text-type check and a substring search answer separate questions: a cell can contain text without containing the particular word you are looking for. Be clear about which question your formula needs to answer before choosing a test.
The Basic Formula: SEARCH and ISNUMBER
Assume A2 holds the text to inspect and you want to find apple. Enter this formula in another cell:
With the text to inspect in A2 and the search word apple entered directly in the formula, use =ISNUMBER(SEARCH("apple",A2)). If A2 contains Green apples, the result is TRUE. If A2 contains Green pears, the result is FALSE.
SEARCH returns a number when it finds a match: in Green apples, the first character of apple is in position 7. If there is no match, SEARCH returns an error. ISNUMBER is the outer test: it reports TRUE for the numeric position and FALSE when SEARCH does not produce a number. This makes the combined formula suitable for logical tests, filters, and conditional formatting.
The capitalization rule matters. SEARCH is not case-sensitive, so a search for apple also finds Apple, APPLE, or a mixed-case version. That is usually the expected behavior for ordinary word searches. If the search term will change, place it in a separate cell instead of editing the formula each time.
With the text to inspect in A2 and the search term in B1, use =ISNUMBER(SEARCH(B1,A2)). If B1 contains apple and A2 contains Red Apple, the result is TRUE. The formula can then be copied down a column: A2 becomes A3, A4, and so on, while B1 stays fixed if you write it as =ISNUMBER(SEARCH($B$1,A2)). The dollar signs keep the search-term reference anchored as the formula is copied.
Make the Search Case-Sensitive
Use FIND in place of SEARCH when capitalization is meaningful. FIND is case-sensitive and, like SEARCH, returns a position when it locates the text and an error when it does not. Wrapping it in ISNUMBER turns the result into TRUE or FALSE.
With the text to inspect in A2 and the case-sensitive search term Apple typed into the formula, use =ISNUMBER(FIND("Apple",A2)). If A2 contains Red Apple, the result is TRUE. If it contains Red apple with a lowercase a, the result is FALSE.
Choose the function based on the data and the question, not on which name sounds more precise. For product names, descriptions, and most general text searches, ignoring case is often useful. For codes or labels where capitalization carries meaning, FIND may be the better choice. FIND does not treat the asterisk and question mark as SEARCH wildcards; it looks for those characters as text.
Substring Search Versus an Exact Match
A contains test is not the same as an exact-match test. If the whole contents of A2 must equal Apple, a direct comparison is simpler than searching inside the cell. Excel’s ordinary text comparison with the equals operator is not case-sensitive.
With the value to compare in A2 and the required complete value Apple typed into the formula, use =A2="Apple". If A2 contains Apple, the result is TRUE; if A2 contains Green Apple, the result is FALSE because the complete value differs. However, this comparison also treats apple as equal to Apple.
For an exact comparison that also distinguishes capitalization, use the EXACT function. With the value in A2 and the required value Apple, use =EXACT(A2,"Apple"). It returns TRUE only when the text matches exactly, including letter case. It will return FALSE for apple and for Green Apple. Consider whether extra spaces are allowed, too: a leading or trailing space can make a value different from the intended exact text.
- Use SEARCH when a substring may appear anywhere and case does not matter.
- Use FIND when a substring may appear anywhere and case does matter.
- Use an equals comparison for a complete, case-insensitive match.
- Use EXACT for a complete, case-sensitive match.
Return a Word Instead of TRUE or FALSE
TRUE and FALSE are useful in formulas, but a worksheet may be easier to read if it displays a label. Combine the contains test with IF to return text such as Found and Not found. This also makes the logic clear to someone reviewing the sheet later.
With the text in A2 and the term apple to find, use =IF(ISNUMBER(SEARCH("apple",A2)),"Found","Not found"). If A2 contains Green apples, the result is Found. If A2 contains Green pears, it is Not found. You can replace those labels with other text, such as Yes and No, without changing the search logic.
For a repeated check, put the search term in B1 and keep it there while copying the formula down the results column. With text in A2 and the term in B1, use =IF(ISNUMBER(SEARCH($B$1,A2)),"Found","Not found"). The dollar signs hold B1 in place; the A2 reference changes by row. If the search term changes, the results recalculate automatically.
Check Cells With Conditional Formatting
If the goal is to spot matching cells visually rather than create a TRUE/FALSE column, use a formula-based conditional-formatting rule. In Excel for Microsoft 365 on Windows, select the cells to format, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter a formula that refers to the first cell in the selected range, choose a format, and confirm the rule.
For example, if A2:A100 is selected and each row should be checked for apple, use =ISNUMBER(SEARCH("apple",A2)) as the rule formula. A2 is the top-left cell of the selected range, so Excel adjusts the reference for each row. Choose a fill color or font style that makes matching cells easy to see. SEARCH makes this rule case-insensitive; replace it with FIND for a case-sensitive rule.
Conditional formatting changes how qualifying cells look; it does not replace their contents or add a permanent label. If you need to sort, count, or filter matching records later, a helper column containing the TRUE/FALSE formula may be more practical. In Excel for Mac or the web, the rule is also available through Conditional Formatting, although the dialogs and ribbon layout can differ from Windows.
Search Several Cells and Count Matches
To count how many cells in a list contain a word, COUNTIF can use asterisks as wildcards. An asterisk matches any number of characters, including none. This makes the pattern *apple* match text with characters before or after apple, as well as apple by itself.
With the values in A2:A10 and apple as the term to count, use =COUNTIF(A2:A10,"*apple*"). If three cells in that range contain apple in any capitalization, the result is 3. COUNTIF text matching is not case-sensitive. It counts matching cells, not the number of times the word appears within each cell.
To use a term stored in B1, join the wildcard characters to the cell reference. With the values in A2:A10 and the search term in B1, use =COUNTIF(A2:A10,"*"&B1&"*"). If B1 contains apple and three cells match, the result is 3. This is convenient for an interactive search box, but remember that COUNTIF treats asterisks and question marks in criteria as wildcard characters. If users might search for those symbols literally, use an escaped SEARCH approach instead.
With the text to inspect in A2 and a literal search term in B1 that may include asterisk, question mark, or tilde characters, use =ISNUMBER(SEARCH(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"~","~~"),"*","~*"),"?","~?"),A2)). The substitutions escape SEARCH’s wildcard symbols so they are treated as literal characters. This longer version is only needed for searches where those special characters may be part of the term.
Common Problems and Misconceptions
- Using SEARCH by itself: SEARCH returns a position or an error, not a tidy TRUE/FALSE answer. Wrap it in ISNUMBER for a logical result.
- Expecting case sensitivity: SEARCH ignores case. Choose FIND if uppercase and lowercase need to differ.
- Confusing “contains” with “equals”: A search for cat can match a longer word such as catalog. Use an exact comparison if the entire cell must match.
- Forgetting wildcards: SEARCH treats * and ? as wildcard characters. Use the escaping formula above if the query must find those symbols literally.
- Searching what is displayed rather than what is stored: A number or date may have a special display format. A text search works on the value Excel evaluates, which may not be the same as the formatted appearance. If the display representation is essential, create text explicitly and search that text.
- Overlooking errors or spaces: If a source cell already contains an error, a search may not produce the expected result. Extra spaces can also prevent exact matches or make a term appear separated from nearby text. Inspect the source values when a result seems surprising.
One other distinction is worth keeping in mind: a formula that returns TRUE does not alter the original cell or extract the matching word. It answers only whether the search criterion is satisfied. Use a separate extraction function or a different workflow if the task is to return the text around a match.
Frequently Asked Questions
How do I check whether a cell contains text in Excel?
To check for a particular word or phrase, use =ISNUMBER(SEARCH("word",A2)), replacing word with the search term and A2 with the cell to inspect. It returns TRUE when the term appears and FALSE when it does not.
How do I make an Excel contains formula case-sensitive?
Replace SEARCH with FIND. For example, with the text in A2 and the required capitalization Apple, =ISNUMBER(FIND("Apple",A2)) returns TRUE for Apple but not apple.
How can I check whether a cell exactly matches a string?
Use an equals comparison for a case-insensitive exact match, or EXACT for a case-sensitive one. An exact match does not accept extra words before or after the required text.
Can Excel highlight cells that contain a word?
Yes. Select the range, create a formula-based rule from Home > Conditional Formatting > New Rule, and use an ISNUMBER(SEARCH(…)) formula that refers to the range’s top-left cell.
Why does my formula find a word inside a longer word?
SEARCH looks for a sequence of characters anywhere in the cell. It does not automatically check word boundaries, so a search for cat can match catalog. If whole words matter, use a method that checks the characters around the match or structure the data to separate words consistently.
Related
- Data Validation Checklist for Excel: What to Check
- Conditional Formatting in Microsoft Project: What Works
- Data Validation and Verification in Excel Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Text Filter for Multiple Words: How It WorksTo filter Excel text for multiple words, open the column’s filter menu and choose Text Filters > Custom…
- Excel Formula for Matching Values: Exact Match GuideFor an exact match in a list, use =MATCH(E2,A2:A100,0): it returns the matching item’s position within the range,…
- Excel AND and OR Functions Combined: How to Use ThemYes. You can combine Excel’s AND and OR functions by nesting one inside the other. For example, with…
- Excel MIN IF: Find the Smallest Value That Meets a TestTo find the smallest value in Excel that meets a condition, use MINIFS in Microsoft 365: with regions…
- Unique Excel Formula With Multiple Criteria: How It WorksTo return distinct values that meet multiple conditions in Excel for Microsoft 365, combine UNIQUE with FILTER. With…
- Excel SUM Ignore Errors: Formulas for Clean TotalsYes. To sum numbers while treating error cells as zero in Microsoft Excel for Microsoft 365, use =SUM(IFERROR(B2:B6,0));…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.