Wildcards
Wildcards are characters that stand in for other characters, so a criterion or lookup value can match part of a text instead of the whole text.
Last updated: 14 October 2026
Explains: wildcard
What it is: A wildcard is a character in a text pattern that stands for other characters: * for any run of characters, ? for exactly one. The pattern Bak* matches Bakker and Bakkerij.
Why it matters: Excel normally compares text as a whole: the criterion Bakker finds a cell that says exactly Bakker. With a wildcard you can count, add up or look up on part of a text, for example every description that contains correction or every entry number that starts with 25-04, without helper columns full of LEFT, MID and FIND.
What wildcards are
A wildcard is a character in a text pattern that stands in for other characters. A pattern such as *correction* matches every text that contains the word correction, wherever it sits.
Three rules apply to every pattern:
- Excel compares the pattern with the whole text in the cell:
25-04finds only25-04itself, while25-04*also finds25-0412 - upper and lower case do not matter
- only text matches: a cell that holds the number
2504is not text, so no pattern finds it
Excel knows three wildcards:
| Character | Stands for | Example pattern | Matches |
|---|---|---|---|
* | Any number of characters, including none | 25-04* | 25-04, 25-0412 and 25-0499, but not 24-0412 |
? | Exactly one character | 25-04?? | 25-0412 and 25-0499, but not 25-04 or 25-04123 |
~ | Takes the special meaning from the next character (* ? or ~), so it is read as itself | 25~* | Only the text 25* with a real asterisk |
Try it yourself
Pick a column, type a pattern and see which cells COUNTIF counts.
Search in
Column A holds the entry numbers. Try the patterns that fit a number.
Column B holds the descriptions. Try a pattern that contains a word.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Entry no. | Description | |||
| 2 | 25-04 | Opening balance | Matches | 44=COUNTIF(A2:A8, "x")=COUNTIF(A2:A8; "x") | |
| 3 | 25-0412 | Correction VAT March | |||
| 4 | 25-0499 | Bank charges April | |||
| 5 | 24-0412 | Accrual correction | |||
| 6 | 25-04123 | Rent April | |||
| 7 | 25* | Draft, number to follow | |||
| 8 | 25-0507 | VAT correction Q1 |
Swipe sideways to see all columns
- Formula cell
- Meets the criterion
=COUNTIF(A2:A8, "25-04*")
=COUNTIF(A2:A8; "25-04*")
Result: 44
=COUNTIF(B2:B8, "25-04*")
=COUNTIF(B2:B8; "25-04*")
Result: 00
- matched as typed
- taken by *
- taken by ?
Contains, starts with, ends with
Most patterns have one of three shapes. To use a value from a cell, join the pieces with &, so the pattern follows whatever the cell says.
Contains: a * on both sides.
=COUNTIF(B2:B100,"*" & E1 & "*")
=COUNTIF(B2:B100;"*" & E1 & "*")
Counts the cells in B2:B100 that contain the text in E1.
Starts with: a * after the text.
=COUNTIF(A2:A100,E1 & "*")
=COUNTIF(A2:A100;E1 & "*")
Counts the cells in A2:A100 that start with the text in E1.
Ends with: a * before the text.
=COUNTIF(A2:A100,"*" & E1)
=COUNTIF(A2:A100;"*" & E1)
Counts the cells in A2:A100 that end with the text in E1.
Does not contain: the operator <> in front of the pattern.
=COUNTIF(B2:B100,"<>*" & E1 & "*")
=COUNTIF(B2:B100;"<>*" & E1 & "*")
Counts the cells that do not contain the text in E1. Empty cells are counted as well.
Where wildcards work
Wildcards work in functions that compare text with a criterion or a lookup value, and in the Find and Replace window (Ctrl+F). Some lookups only read them after you choose the right mode.
| Function | Where the pattern goes | Condition |
|---|---|---|
COUNTIF, COUNTIFS, SUMIF, SUMIFS | The criteria | Always, on text cells. |
AVERAGEIF, AVERAGEIFS, MAXIFS, MINIFS | The criteria | Always, on text cells. |
MATCH | The lookup value | Only with match type 0 (exact match). |
XLOOKUP, XMATCH | The lookup value | Only with match mode 2. With the default 0 the asterisk is searched as a character. |
VLOOKUP | The lookup value | Only for an exact match (last argument FALSE or 0). |
SEARCH | The text to find | Always. SEARCH also ignores upper and lower case. |
=XLOOKUP("*" & E1 & "*",A2:A8, B2:B8, "Not found", 2)
=XLOOKUP("*" & E1 & "*";A2:A8; B2:B8; "Not found"; 2)
Match mode 2 switches wildcards on. It is the fifth argument in XLOOKUP and the third in XMATCH.
=MATCH("*" & E1 & "*",A2:A8, 0)
=MATCH("*" & E1 & "*";A2:A8; 0)
MATCH reads wildcards when the match type is 0. It returns the position of the first cell that fits.
Where wildcards do not work
Watch out: Excel gives no warning when a wildcard does not work. It reads * and ? as ordinary characters, finds nothing, and returns a zero or a #N/A. The formula still looks sensible, which makes this the most common wildcard mistake.
The table lists where * and ? count as ordinary characters, and what to use instead.
| Function or situation | What happens | Use instead |
|---|---|---|
FIND | * and ? are searched as real characters. | SEARCH, which reads wildcards. Keep FIND when the search must be case sensitive. |
| The = and <> operators | A2 = "*x*" compares the cell with the text *x* itself. | COUNTIF, or ISNUMBER(SEARCH(...)) when you need a TRUE or FALSE per cell. |
EXACT | Compares both texts character by character. | Nothing: EXACT is meant for exact, case-sensitive comparison. |
FILTER | The include argument of FILTER B2:B9 = "*x*" is a comparison, so no row passes. | ISNUMBER(SEARCH(...)) as the include argument. |
SUBSTITUTE | Replaces the text *x* itself, not a pattern. | Find and Replace (Ctrl+H), which reads wildcards, or SEARCH to find the position and REPLACE to change it. |
| Numbers | A pattern matches text only, so 25* never matches the number 2504. | Comparison operators on the number, or convert the number to text first. |
=FILTER(A2:B9,ISNUMBER(SEARCH("correction", B2:B9)))
=FILTER(A2:B9;ISNUMBER(SEARCH("correction"; B2:B9)))
Returns the rows whose description contains the word. SEARCH supplies the TRUE or FALSE per row that FILTER needs.
Worked examples
Example1 of 3Beginner
Count the correction entries in the journal
You received the journal of Veldhuis Installatie BV as a sheet. The bookkeeper marks corrections by putting the word correction somewhere in the description, sometimes at the start, sometimes in capitals in the middle.
Before testing journal entries you want to know how many correction entries there are.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Entry no. | Description | Corrections | ||
| 2 | 25-0601 | Office rent October | 44=COUNTIF(B2:B9, "*correction*")=COUNTIF(B2:B9; "*correction*") | ||
| 3 | 25-0602 | Correction office rent October | |||
| 4 | 25-0603 | Purchase cables Bakker Elektro | |||
| 5 | 25-0604 | Correction cable purchase | |||
| 6 | 25-0605 | Maintenance sales Q3 | |||
| 7 | 25-0606 | Correction maintenance sales | |||
| 8 | 25-0607 | Fuel company vehicles | |||
| 9 | 25-0608 | VAT CORRECTION Q3 | |||
| 10 |
Swipe sideways to see all columns
Formula in D2
=COUNTIF(B2:B9,"*correction*")
=COUNTIF(B2:B9;"*correction*")
Result: 44
Step by step
- The criterion is the text
*correction*. The first*stands for any characters before the word, including none, and the second*for any characters after it. - COUNTIF tests the eight cells of
B2:B9one by one and ignores upper and lower case.B2(Office rent October) has no correction: no.B3(Correction office rent October) does: yes, the first*matches nothing. B4(Purchase cables Bakker Elektro): no.B5(Correction cable purchase): yes.B6(Maintenance sales Q3): no.B7(Correction maintenance sales): yes.B8(Fuel company vehicles): no.B9(VAT CORRECTION Q3): yes. The capitals do not matter, the first*matches"VAT "and the second*matches" Q3".- Four cells pass (
B3,B5,B7andB9), so the result is 4. - Without the asterisks the criterion is the whole text
correction, and the result would be 0: no cell says only that. With onlycorrection*at the end the result is 3, becauseB9starts withVAT.
Watch out: The pattern matches the letters you typed, not the meaning. A description such as Corr. rent is not counted, so the 4 can understate the corrections. Scan the unmatched rows once, or count with a shorter pattern such as *corr* and compare.
More on this example: why it works, 2 variations
Why it works
COUNTIF compares each cell with the whole pattern, and a * can take the place of any number of characters, including none.
Putting a * on both sides turns "equal to" into "contains", so the position of the word in the description no longer matters.
Variations
=COUNTIF(B2:B9,"correction*")
=COUNTIF(B2:B9;"correction*")
Starts with: counts only the descriptions that begin with the word. The result is 3, because B9 begins with VAT.
=SUMPRODUCT(--ISNUMBER(SEARCH("correction",B2:B9)))
=SUMPRODUCT(--ISNUMBER(SEARCH("correction";B2:B9)))
Counts the same four cells without a wildcard pattern. Use it when the condition gets more complicated than a single criterion.
Example2 of 3Intermediate
Find the creditor number from part of a supplier name
The bank statement of Veldhuis Installatie BV shows a payment to "DE WIT", cut off by the bank. You want the creditor number in the client's creditor list, and you only have the fragment.
E1 holds the fragment. When no supplier fits, a not-found message should appear.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Supplier | Creditor no. | Search text | wit | ||
| 2 | Bakker Elektro BV | C2001 | Creditor no. | C2003=XLOOKUP("*" & E1 & "*", A2:A8, B2:B8, "Not found", 2)=XLOOKUP("*" & E1 & "*"; A2:A8; B2:B8; "Not found"; 2) | ||
| 3 | Kooiman Groothandel BV | C2002 | ||||
| 4 | De Wit Isolatie | C2003 | ||||
| 5 | Van Dijk Kabels BV | C2004 | ||||
| 6 | Jansen Dakbedekking | C2005 | ||||
| 7 | Dijkstra Transport | C2006 | ||||
| 8 | Smit Installatiemateriaal | C2007 | ||||
| 9 |
Swipe sideways to see all columns
Formula in E2
=XLOOKUP("*" & E1 & "*",A2:A8, B2:B8, "Not found", 2)
=XLOOKUP("*" & E1 & "*";A2:A8; B2:B8; "Not found"; 2)
Result: C2003
Step by step
E1holdswit. The expression "*" & E1 & "*" joins the pieces into the text*wit*, which is the lookup value Excel works with.- The fifth argument is 2, the match mode for wildcards. With the default 0, Excel would search for a supplier literally called
*wit*and find none. - XLOOKUP reads
A2:A8from the top.A2(Bakker Elektro BV): no.A3(Kooiman Groothandel BV): no.A4(De Wit Isolatie): yes, the capitalWis ignored, the first*matches"De "and the second*matches" Isolatie". - The match is the third item, so XLOOKUP returns the third item of
B2:B8, which isB4:C2003. - Type
xyzinE1and no supplier fits: the fourth argument showsNot foundinstead of #N/A.
Watch out: A fragment can fit more than one supplier. With dijk in E1, the formula returns C2004 (Van Dijk Kabels BV), the first fit, although A7 (Dijkstra Transport) fits too. Count the fits first with the COUNTIF variation below.
More on this example: why it works, 1 more pitfall, 2 variations
Why it works
The match mode makes the difference: it tells XLOOKUP to read * and ? in the lookup value as wildcards instead of characters.
Joining the asterisks to a cell with & keeps the formula reusable, because you only change E1.
More to watch out for
- An empty
E1gives the pattern**, which fits any text. The formula then quietly returnsC2001(the first supplier). Check thatE1is filled before you trust the result.
Variations
=COUNTIF(A2:A8,"*" & E1 & "*")
=COUNTIF(A2:A8;"*" & E1 & "*")
Counts how many suppliers fit the fragment. With wit the result is 1; with dijk it is 2. Run it before you rely on a single answer.
=IFNA(INDEX(B2:B8,MATCH("*" & E1 & "*", A2:A8, 0)), "Not found")
=IFNA(INDEX(B2:B8;MATCH("*" & E1 & "*"; A2:A8; 0)); "Not found")
The same result in Excel versions without XLOOKUP: MATCH with match type 0 reads the wildcards and INDEX fetches the creditor number.
Example3 of 3Advanced
Count the entries with a real question mark or asterisk
The bookkeeper of Veldhuis Installatie BV types a ? in the description when a posting is unclear, and sometimes a * for a note.
You want to count the entries that contain the character in E1. The formula has to work for both characters, because both are wildcards.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Entry no. | Description | Character to find | ? | ||
| 2 | 25-0701 | Boiler maintenance ? | Entries | 33=COUNTIF(B2:B9, "*" & SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E1, "~", "~~"), "*", "~*"), "?", "~?") & "*")=COUNTIF(B2:B9; "*" & SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E1; "~"; "~~"); "*"; "~*"); "?"; "~?") & "*") | ||
| 3 | 25-0702 | Office rent November | ||||
| 4 | 25-0703 | Cash deposit ?? | ||||
| 5 | 25-0704 | Invoice cables * credit note | ||||
| 6 | 25-0705 | Fuel company vehicles | ||||
| 7 | 25-0706 | Is this a lease payment? | ||||
| 8 | 25-0707 | Telephone costs | ||||
| 9 | 25-0708 | Salary correction (*) | ||||
| 10 |
Swipe sideways to see all columns
Formula in E2
=COUNTIF(B2:B9,"*" & SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E1, "~", "~~"), "*", "~*"), "?", "~?") & "*")
=COUNTIF(B2:B9;"*" & SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E1; "~"; "~~"); "*"; "~*"); "?"; "~?") & "*")
Result: 33
Step by step
E1holds?. IfE1went into the criterion as it is, COUNTIF would read the?as "any one character" instead of a question mark. The three SUBSTITUTE functions put a~in front of each special character first.- The innermost SUBSTITUTE doubles any tilde in
E1(~becomes~~).E1has none, so the text stays?. This comes first on purpose: the tildes added by the next two steps must not be doubled. - The middle SUBSTITUTE puts
~in front of every*, andE1has none: still?. The outer SUBSTITUTE puts~in front of every?, so the text becomes~?. - "*" & "~?" & "*" makes the pattern
*~?*: any text, then a real question mark, then any text. - COUNTIF tests
B2:B9. The cells with a real question mark areB2(Boiler maintenance ?),B4(Cash deposit ??) andB7(Is this a lease payment?).B5andB9have an asterisk but no question mark, and the other cells have neither. The result is 3. - Without the escaping, the pattern
*?*means "at least one character" and the formula counts all 8 cells. Type*inE1instead and the pattern becomes*~**(any text, a real asterisk, any text): the result is 2, forB5andB9.
Watch out: The mistake looks right: "*" & E1 & "*" with a ? in E1 returns 8, the number of non-empty cells, and a count that looks plausible gets accepted quickly. Test the formula on a character you know occurs only a few times.
More on this example: why it works, 1 more pitfall, 3 variations
Why it works
The tilde makes the next character mean itself. Because the characters to find sit in a cell, the formula has to add the tildes itself.
The order matters: tildes first, then * and ?, so that a tilde in E1 survives and the added ones are not doubled.
More to watch out for
- If you only need one fixed character, write the escape by hand, as in the first variation. The SUBSTITUTE chain is for a character that comes from a cell.
Variations
=COUNTIF(B2:B9,"*~?*")
=COUNTIF(B2:B9;"*~?*")
A fixed character: type the tilde yourself. The result is 3.
=SUMPRODUCT(--ISNUMBER(FIND(E1,B2:B9)))
=SUMPRODUCT(--ISNUMBER(FIND(E1;B2:B9)))
No escaping needed, because FIND reads no wildcards. It is case sensitive, which does not matter for ? and *. The result is 3.
=LET( txt,E1, safe, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(txt, "~", "~~"), "*", "~*"), "?", "~?"), COUNTIF(B2:B9, "*" & safe & "*") )
=LET( txt;E1; safe; SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(txt; "~"; "~~"); "*"; "~*"); "?"; "~?"); COUNTIF(B2:B9; "*" & safe & "*") )
The same formula with LET, which gives the escaped text a name. Clearer to read and to reuse when the formula grows.
Common mistakes
| Symptom | Cause | Fix |
|---|---|---|
| COUNTIF with 40* returns 0 on account numbers | You count account numbers with COUNTIF(A2:A9, "40*") and get 0, although the sheet has accounts 4000 to 4099. | Account numbers stored as numbers are not text, so a wildcard never matches them. Use two comparison criteria, ">=4000" and "<4100", or convert the column to text first. |
| A pattern misses entries that start with a space | A pattern such as "correction*" misses descriptions that start with a space, and a ? counts a space as a character. | To fix it:
|
| COUNTIF with * also counts cells with empty text | COUNTIF(A2:A9, "*") is meant to count the filled cells, but it also counts cells with a formula that returns empty text. | A Numbers and truly blank cells are never counted by either pattern. |
| A search for capitals also returns lower case matches | You search for BTW and the result also includes btw and Btw, or you need exactly the capitals and get too many. | Wildcard matching ignores case in every function that reads it. For a case-sensitive count use SUMPRODUCT(--ISNUMBER(FIND(...))), where FIND is case sensitive and reads no wildcards. |
| XLOOKUP returns #N/A although a name contains the text | XLOOKUP("*wit*", A2:A8, B2:B8) returns #N/A although a name contains wit. | Without the fifth argument the match mode is 0, and the asterisks are searched as characters. Add 2 as the match mode (and a message as the fourth argument). |