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

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.

See Excel Wildcard Characters – What are these and How to Best Use it
Excel Wildcard