To check whether text in A2 contains the search term in E2, use =ISNUMBER(SEARCH(E2,A2)). It returns TRUE when the term is found and FALSE when it is not; SEARCH is case-insensitive and can also use wildcard characters such as * and ?.
What Excel wildcards mean
A wildcard stands in for one or more characters in a text pattern. Excel’s main wildcard characters are:
*matches any number of characters, including none. The patternNorth*can match North, Northern, or Northwind.?matches exactly one character. The patternc?tcan match cat, cot, or cut, but not cart.~escapes a wildcard so Excel treats it as a literal character. Use~*to search for an actual asterisk and~?for an actual question mark.
Wildcards are useful when you know only part of a value, or when spelling, spacing, or suffixes vary. They are not the same as regular expressions: Excel’s wildcard symbols have these specific meanings, and a pattern does not provide the full set of rules found in a regex search.
Use SEARCH to test one cell
SEARCH returns the position where a text string begins inside another string. If the text is absent, it returns an error. ISNUMBER turns that result into a straightforward TRUE or FALSE test. With a description in A2 and the word cat in E2, the opening formula returns TRUE for “A cat is sleeping” and FALSE for “A dog is sleeping.” It also finds cat within a longer word, such as “catalog.”
SEARCH is already a partial-text search: you do not need to add asterisks around a plain search term. For example, searching for cat finds cat inside “black cat.” Wildcards matter when the pattern itself needs to allow for uncertain characters. With a pattern in E2 and the text in A2, the same structure can test a wildcard pattern:
With A2 containing “The cot is by the window” and E2 containing c?t, use =ISNUMBER(SEARCH(E2,A2)). It returns TRUE because ? stands for the single o between c and t.
SEARCH ignores uppercase and lowercase differences. Use FIND instead if the match must be case-sensitive, but FIND does not support wildcard matching. For ordinary case-insensitive wildcard searches, SEARCH is generally the more direct choice.
Count cells containing a phrase
COUNTIF is handy when you need a count rather than a TRUE or FALSE result. Its criteria can include wildcards, and asterisks on both sides of a word mean that the word may appear anywhere in each cell. The match is not case-sensitive.
With locations in A2:A5—Northwind, South Depot, North Store, and Northeast—use =COUNTIF(A2:A5,"*North*"). The result is 3: Northwind, North Store, and Northeast contain North. The asterisk on either side allows other characters before or after the search text.
COUNTIF checks each cell against a criterion; it does not return the matching text. If you want to inspect which rows match, use a separate helper formula, a filter, or a dynamic-array formula such as FILTER in a supported Excel version. Also remember that COUNTIF is case-insensitive, so it cannot distinguish North from north.
Sum values for partial text matches
SUMIF applies the same wildcard idea when you want to add values associated with matching labels. Its first argument is the range to check, its second is the criterion, and its third is the range to add. The criteria range and sum range should be aligned row by row.
With labels in A2:A5 and amounts in B2:B5—120, 80, 45, and 30 respectively, corresponding to Northwind, South Depot, North Store, and Northeast—use =SUMIF(A2:A5,"*North*",B2:B5). The result is 195, calculated as 120 + 45 + 30. South Depot is excluded because its label does not contain North.
This is useful for summaries where names have changing endings, such as department labels or product descriptions. It does not identify which rows contributed to the total, so verify the source range if the result is unexpected. For a case-sensitive sum, SUMIF’s wildcard criteria are not sufficient; a more tailored formula is needed.
Return a matching value with XLOOKUP
If you want Excel to return a value from a matching row, XLOOKUP can use wildcard matching when its match-mode argument is 2. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. The wildcard pattern goes in the lookup-value position; the final 2 tells XLOOKUP to interpret wildcard characters.
With product names in A2:A4 (Blue mug, Red mug, Green cup), prices in B2:B4 (8, 9, 6), and mug in E2, use =XLOOKUP("*"&E2&"*",A2:A4,B2:B4,"Not found",2). It returns 8, the price on the first row whose name contains mug. If multiple rows match, XLOOKUP returns the first match in the lookup range.
Here the ampersands join the asterisks to the text in E2. Without match mode 2, XLOOKUP does not interpret the asterisks as wildcard instructions. If you need all matching rows rather than the first one, FILTER can be a better fit in Microsoft 365 or Excel 2021 and later.
Search for a literal asterisk or question mark
Sometimes the symbol itself is part of the data—for example, an item code that includes an asterisk. In wildcard criteria, place a tilde before the symbol to escape it. With text in A2, use =ISNUMBER(SEARCH("~*",A2)) to test for a literal asterisk. If A2 contains “Plan*Update,” the result is TRUE.
To find a literal question mark in A2, use =ISNUMBER(SEARCH("~?",A2)). For a literal tilde, double it in the search pattern: =ISNUMBER(SEARCH("~~",A2)). These examples assume the target text is in A2 and return TRUE if the escaped character is present. Escaping is important in COUNTIF and other wildcard criteria too; otherwise, an asterisk or question mark is treated as a pattern rather than as punctuation.
Handle blank searches and common errors
A blank search term can create a misleading result. SEARCH with an empty string finds a match at the start of the text, so the basic test can return TRUE when E2 is blank. If you want blank input to produce a blank result, with text in A2 and the search term in E2, use =IF(E2="","",ISNUMBER(SEARCH(E2,A2))). This returns an empty cell result when E2 is empty; otherwise, it returns TRUE or FALSE.
Other issues to check include:
- Unexpected matches: SEARCH ignores case and looks inside longer words. Searching for art also matches “carton.” Add boundaries or use a more specific pattern if that is not wanted.
- Wildcards in the search term: If E2 contains * or ?, SEARCH treats those characters as wildcards. Escape them with a tilde when they are meant literally.
- An error in the source cell: SEARCH can pass an existing cell error through rather than return TRUE or FALSE. Fix the source error or deliberately handle errors in a separate formula.
- Wrong range alignment: In SUMIF, make sure each label and amount refer to the same row and that the ranges cover the intended records.
Excel for Microsoft 365 on Windows uses commas between formula arguments in these examples. The formulas also work in Excel for Mac and Excel for the web, though regional settings can use a different list separator, such as a semicolon. If a pasted formula is rejected, check the separator expected by your Excel installation.
Choose the right search approach
The best formula depends on what the result needs to do, not just on whether the text is partial. Use SEARCH with ISNUMBER for a per-row yes-or-no test, COUNTIF to count matching cells, SUMIF to add values tied to matching labels, and XLOOKUP when the first matching row should supply a result.
- Choose SEARCH when you want to combine a text test with other logic or display a match flag beside each record.
- Choose COUNTIF for a quick count based on a wildcard criterion.
- Choose SUMIF when a partial label determines which numeric values to add.
- Choose XLOOKUP when you need one corresponding result and the first match is appropriate.
These approaches are not interchangeable in every situation. COUNTIF and SUMIF use wildcard criteria directly, while XLOOKUP needs wildcard match mode enabled. SEARCH tests the pattern within one cell and can be combined with other functions. For a simple contains check, SEARCH is often clearest; for a summary, a conditional function avoids building a separate test column.
Frequently Asked Questions
Does Excel SEARCH support wildcards?
Yes. SEARCH recognizes * for any number of characters and ? for one character. Use a tilde to search for either symbol literally. SEARCH is case-insensitive.
What formula finds text anywhere in a cell?
With the cell to inspect in A2 and the search text in E2, =ISNUMBER(SEARCH(E2,A2)) returns TRUE if E2 occurs anywhere in A2, including within a longer word.
How do I count cells that contain a word?
With the cells to check in A2:A10, use =COUNTIF(A2:A10,"*word*"). Replace word with the text to find. The result is the number of cells containing it, without regard to case.
Can I make a wildcard search case-sensitive?
SEARCH and the wildcard criteria in COUNTIF are case-insensitive. FIND is case-sensitive but does not support wildcards, so combining case sensitivity with flexible patterns requires a different formula design.
Why does my wildcard formula match too many cells?
An asterisk can represent any number of characters, and a contains pattern can match a word embedded inside a longer word. Narrow the pattern, escape wildcard symbols that are literal data, or use a more specific test.
Related
- PHP Backend Data Validation: A Practical Guide
- Protect Sheet in Excel: Allow Editing in Certain Cells
- Jira Merge Table Cells: Steps and Limitations
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- VLOOKUP Examples Using Two SpreadsheetsTo look up a value in a separate Excel workbook, put the lookup key in your current sheet…
- Excel Timesheet Formula With Breaks and Overnight ShiftsWith a shift start in B2, shift end in C2, break start in D2, and break end in…
- Excel Formula for Age From Date of BirthThe simplest Excel formula for age from a date of birth is =DATEDIF(A2,TODAY(),"Y"), assuming the birth date is…
- How to Count Unique Values in an Excel PivotTableTo count distinct values in an Excel PivotTable, create the PivotTable with Add this data to the Data…
- NPV Calculator in Excel: Formula, Example, and TipsTo calculate net present value (NPV) in Excel, enter the discount rate and future cash flows, then use…
- Excel XLOOKUP Between Two Worksheets: ExamplesIf your lookup value is on one worksheet and the data to search is on another, XLOOKUP can…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.