To check whether a cell contains specific text in Excel, use IF with SEARCH and ISNUMBER. With the text to check in A2, this formula returns “Yes” when A2 contains “urgent” and “No” otherwise: =IF(ISNUMBER(SEARCH("urgent",A2)),"Yes","No"). It is case-insensitive, so it also finds “Urgent.”
The basic contains-text formula
The formula works by combining three functions. SEARCH looks for one string inside another and returns the position where it finds the match. ISNUMBER turns that result into TRUE when a position is returned and FALSE when the text is not found. IF then displays the result you choose for TRUE or FALSE.
For example, with A2 containing Urgent: call the customer, the formula in the opening paragraph returns Yes. If A2 contains Call the customer tomorrow, it returns No. The output can be words, numbers, or another formula, so you could return a status such as “Review,” a value such as 1, or an empty string.
To use it in a worksheet:
- Click the cell where you want the result, such as B2.
- Enter the formula, changing A2 to the cell you want to inspect and changing “urgent” to your search text.
- Press Enter. To check more rows, drag the fill handle down or copy and paste the formula into the other result cells.
When copied down one row, the reference changes from A2 to A3 automatically. If the search term is stored in another cell, use a cell reference instead of typing it into the formula. For instance, with the term in D1 and the text in A2, use =IF(ISNUMBER(SEARCH($D$1,A2)),"Yes","No"). The dollar signs keep the search-term reference fixed as you copy the formula down.
Choosing between SEARCH and FIND
Use SEARCH for a case-insensitive check. It treats “urgent,” “Urgent,” and “URGENT” as matches. This is usually the expected behavior when checking descriptions, notes, product names, or other ordinary text.
Use FIND instead when capitalization must match. With the text to inspect in A2, this formula returns “Yes” for Excel guide but “No” for excel guide:
With the text to inspect in A2, and the exact capitalization “Excel” required, enter: =IF(ISNUMBER(FIND("Excel",A2)),"Yes","No").
Both functions return an error when they cannot find the requested text. In the common pattern, ISNUMBER handles that result as FALSE, allowing IF to return the alternative value. One practical distinction is that SEARCH recognizes wildcard characters: an asterisk can stand for any sequence of characters and a question mark can stand for one character. Use a tilde to search for a wildcard character literally—for example, ~* to look for an asterisk. FIND does not treat asterisk and question mark as wildcards.
Contains text is different from an exact match
A contains check can find the search term anywhere inside a longer cell value. If A2 reads urgent follow-up, a search for “urgent” matches. If the requirement is that the entire cell must equal the word, use an equality test instead. With A2 expected to contain exactly the word urgent, enter: =IF(A2="urgent","Yes","No"). That formula returns No for urgent follow-up.
Excel’s ordinary text equality comparison is not case-sensitive. If both a full-cell match and matching capitalization are required, combine equality with EXACT. With A2 expected to be exactly the lowercase word urgent, use: =IF(EXACT(A2,"urgent"),"Yes","No"). This returns Yes only for the same text with the same capitalization. Choose the test that reflects the rule: “contains,” “equals,” and “equals with case” are different conditions.
Counting cells that contain text
If the goal is to count matching cells rather than label each row, COUNTIF is a compact option. Its asterisk wildcard represents any number of characters, so placing an asterisk before and after a word finds it anywhere in a cell. With descriptions in A2:A10, enter: =COUNTIF(A2:A10,"*urgent*"). If three cells in that range contain urgent, the result is 3.
This is useful for a quick total, but it does not identify which rows matched or return a custom label beside each row. Use the IF-and-SEARCH formula when you need a per-row result. Also note that COUNTIF’s wildcard matching is not case-sensitive. If the text to count is held in D1, a criterion can be assembled with concatenation: with descriptions in A2:A10 and the search term in D1, enter =COUNTIF(A2:A10,"*"&D1&"*"). If D1 contains urgent, the formula counts cells containing that text.
Checking for one of several words
Sometimes a row should qualify if it contains any one of several terms. Use OR to test each term and let IF return a result if at least one test succeeds. With descriptions in column A, check A2 for either urgent or review using: =IF(OR(ISNUMBER(SEARCH("urgent",A2)),ISNUMBER(SEARCH("review",A2))),"Check",""). If A2 contains Needs review today, the result is Check; if it contains neither word, the result is blank.
For a row that must contain both terms, use AND instead of OR. With the same cell layout, this formula returns “Both” only when A2 contains urgent and review: =IF(AND(ISNUMBER(SEARCH("urgent",A2)),ISNUMBER(SEARCH("review",A2))),"Both","Not both"). These formulas check whether each term appears anywhere; they do not require the words to be next to each other or in a particular order. For more complicated lists of search terms, a helper range or a more deliberate formula design can be easier to maintain than a long chain of conditions.
Highlighting matching cells instead of returning a label
If you want to draw attention to matches directly, use conditional formatting rather than adding a separate result column. Select the cells to check, such as A2:A100, and choose Home > Conditional Formatting > New Rule. Select “Use a formula to determine which cells to format,” enter the formula below, choose a format, and confirm the rule.
With A2:A100 selected and A2 as the first cell in the selection, use: =ISNUMBER(SEARCH("urgent",A2)). The reference is relative, so Excel evaluates each row against its own cell. If you apply the rule to a different range, make the formula’s row and column reference match the first cell of that selection. To highlight entire rows based on text in column A, select the row range and use a column-locked reference such as =ISNUMBER(SEARCH("urgent",$A2)).
Conditional formatting changes how cells look; it does not put a “Yes” or other value into them. The rule is useful for visual scanning, while a formula in a worksheet cell is better when the result needs to be filtered, counted, or used by another calculation.
Common problems and ways to avoid them
- The formula returns No unexpectedly: Check that the cell reference points to the intended data and that the search term is spelled correctly. A contains check does not ignore spaces or punctuation; searching for “order ready” will not match “order-ready.”
- The formula says Yes when you expected an exact match: SEARCH finds a term inside longer text. Use an equality test or
EXACTif the entire cell must match. - Capitalization seems ignored: That is normal for
SEARCH. Switch toFINDfor a case-sensitive contains check. - A blank cell is being treated as a match: An empty search string can be found at the start of text. If the search term comes from a cell, check that it is not blank before searching.
- The source cell contains an error: An error in the source can pass through the search formula rather than produce a normal Yes or No.
IFERRORcan provide a fallback, but it can also hide source-data errors that you may need to investigate. - The formula displays as text: Confirm that the cell is not formatted as Text, remove any leading apostrophe, and re-enter the formula. Also check that calculation is not being confused with a value pasted as plain text.
If you intentionally want a blank result when the source is blank, test that condition first. With the text in A2, enter: =IF(A2="","",IF(ISNUMBER(SEARCH("urgent",A2)),"Yes","No")). This leaves the result blank for an empty A2 and checks nonempty cells. If A2 contains a formula that returns an empty string, this test also treats it as blank.
Frequently Asked Questions
How do I check whether a cell contains a word in Excel?
With the text in A2, use =IF(ISNUMBER(SEARCH("word",A2)),"Yes","No"). Replace word with the text you are looking for. SEARCH is not case-sensitive.
How do I check if a cell contains text and return a value?
Put the value you want after the TRUE condition in IF. For example, with text in A2, =IF(ISNUMBER(SEARCH("late",A2)),1,0) returns 1 when late appears and 0 otherwise.
How do I make Excel check for text with the same capitalization?
Use FIND instead of SEARCH inside ISNUMBER. FIND is case-sensitive, while SEARCH is not.
Can Excel count cells that contain a word?
Yes. For descriptions in A2:A10, =COUNTIF(A2:A10,"*word*") counts cells containing word anywhere in their text. COUNTIF matching is not case-sensitive.
How do I highlight cells that contain specific text?
Select the range and create a conditional formatting rule using a formula such as =ISNUMBER(SEARCH("urgent",A2)). Make A2 the first cell of the selected range, then choose the format to apply to matches.
Why does a contains formula match part of a longer word?
SEARCH looks for a sequence of characters, not necessarily a complete word. A search for “art” can match “artist.” If you need whole-word matching, define the word boundaries you expect and use a formula suited to those boundaries rather than assuming SEARCH recognizes words automatically.
Related
- Excel Data Modelling: Tables, Relationships and Pivots
- Excel Get Data From a Semantic Model: Connect and Analyze
- Excel Data Model: What It Is and How to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- IFERROR vs ISERROR: Which Excel Function Should You Use?IFERROR catches an error produced by a calculation and returns a replacement value; ISERROR checks whether a value…
- Excel Day Count Formula: Calculate Days Between DatesTo count calendar days between two dates in Excel, subtract the earlier date from the later one: with…
- How to Add Footnotes in a Word Table the Right WayTo add a true footnote to a Word table, click in the table cell where the reference belongs,…
- HLOOKUP Examples for Practice: Formulas and Fixes in ExcelFor an exact-match HLOOKUP practice example, put product codes in A1:D1, prices in A2:D2, and stock counts in…
- Excel IRR Guess: How It Works and When to Change the ValueIn Excel, the optional guess argument in the IRR function is an initial estimate of the rate of…
- Excel Import CSV: Formula or the Right Import MethodExcel does not have a built-in worksheet formula that imports a CSV file from your computer. In Microsoft…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.