Excel wildcard filter

Excel wildcard characters

Three Microsoft Excel wildcard characters are available for filtering: ? for one character, * for zero or more characters, and ~ to escape a wildcard.

They can be used directly in the quick filter and auto filter, in the extended search, including for a search and replace, and also in the formulas.

Excel wildcards

The three available wildcards are the following:

• Putting the character question mark ? will replace any single character,
• Putting the character asterisk * will replace any string, from zero character to any number of characters,
• Using the character tilde ~ will escape a wildcard, meaning the character will be used instead of the wildcard, for example ~? to use the question mark character, ~* to use the asterisk character, and ~~ to use the tilde character.

Excel wildcard * asterisk

If an asterisk character * is placed at the beginning of a search, result will contain all possibilities ending with given string - if placed at the end, result will contain all possibilities starting with the string.

And entering part of a string without a wildcard will return all result, regardless the position of the search in the result.

It is equivalent to using an Excel wildcard * before and after a search string, as in the quick filter it will look for all strings containing the search.

Excel wildcard replace and search

How to look for strings containing a wildcard?

In order to find results containing the wildcards * or ?, preceded these wildcards with the wildcard a tilde sign ~, as below for a search of strings containing one Excel wildcard * asterisk.

Or as below for a string containing ***, three times a star or asterisk, which is also the Excel wildcard *.

Searching for one or more Excel wildcard * asterisk, such as below example with three ***, without the Excel wildcard tilde, will simply return all results, as the asterisk also is the Excel wildcard * which matches any combination of any character.

Excel wildcard characters usage

See below some examples of Excel wildcard characters use:

• ? matches any one single character, such as A, z, 1,
• ?? matches any two characters, like A9, dL, 1m,
• ??? matches any three characters, like a0l, mE4, c:4,
• * matches any character string, including empty string, like , abcd, a3$B,
• *abc matches any string ending with abc, such as 1abc, Mabc,
• abc* matches any string starting with abc, like abc1, abcM,
• ?* matches any string with one character or more, like 1, Abc,
• ??,??? matches any string with 2 characters, one comma, and 3 characters after, like 12,35$, or ab,cde,
• *~? matches any question ending with question mark, like Really?, How are you?,
• *abc* will match any string containing the given string abc, like abc, 1abcm.

Excel wildcard in functions

Become an Excel Pro: Join Our Course!

Elevate your skills from novice to hero with our Excel 365 Basics course, designed to make you proficient in just a few sessions.

Enroll Here

The Excel wildcard characters can be used in most standard functions, like AVERAGEIF, AVERAGEIFS, COUNTIF, COUNTIFS, SUMIF, SUMIFS, VLOOKUP, HLOOKUP, MATCH, SEARCH.

Excel SUMIF cell containing any text can be achieved by using an Excel wildcard in SUMIF function, by using the Excel wildcard * asterisk.

Excel wildcard in IF statement is possible, simply by using the Excel wildcard in the IF function test expression.

Excel find and replace wildcard

In the extended search, which offers the possibility to search and replace, a wildcard can be used in the search, which will perform an Excel find and replace wildcard, by searching a string with one of the three Excel wildcard characters * asterisk, ? question mark, and ~ tilde, in the search box, and replacing the results by a given string.

Excel replace star

To replace an Excel wildcard * star, simply look for it using the ~ tilde wildcard, as the use of the tilde before the start will look for the star character, instead of the Excel wildcard. An example is using this exact search string to find a star wildcard ~*, after what it is possible in Excel to replace the star character with any other character using the search and replace function.

Replace tilde in Excel

Relacing a tilde in Excel can be achieved by doubling the tilde, such as ~~, as tilde is one of the Excel wildcard characters, and is meant to access the following character regardless of its result if used alone.

What is a wildcard search

A wildcard search is the possibility to search for a text string with extra option, such as any character, or a substring in any string.

Wildcard literally means any value or property. In Excel, wildcard characters are use to test the existence or not of any character with the ? question mark wildcard character, or the presence of any string at a given position with the Excel wildcard * asterisk, or the presence of a wildcard character by using the wildcard ~ tilde.

Frequently Asked Questions

How can users apply filters with wildcards in Excel to search for and isolate data that meets specific, flexible criteria?

In Excel, to apply a wildcard filter, use the filter function by clicking on the Data tab > Filter. Then, in the column header dropdown, select Text Filters > Custom Filter. Use wildcards like asterisk (*) for multiple characters and question mark (?) for single characters in your criteria to filter data that matches patterns, enhancing data analysis efficiency.

Complete 2019 Excel for Beginners in video