How to Use FILTER With Wildcards

Excel’s FILTER function filters a set of data based on defined criteria. However, it doesn’t allow for partial matches that use wildcards.

What are the wildcard characters?

• * (asterisk) — represents any number of characters.
• ? (question mark) — a single character.
• ~ (tilde) — used to indicate the next character is literal. Use this if you want to find an asterisk, question mark or tilde.

One workaround is to use FILTER in conjunction with ISNUMBER and SEARCH in the 𝘪𝘯𝘤𝘭𝘶𝘥𝘦 argument.

SEARCH returns the character position of each string in the range.

If it fails to find it, a #VALUE error is produced (image 2).

As the array contains either a number or an error — when it’s fed into the ISNUMBER function, another array of TRUE/FALSE values is generated (image 3). This determines the rows that are returned.

Example 1

Searches for all instances of ‘der’ (see previous images).

Scott Dermott
Sandra Henderson
Raymond van der Hilch

Example 2

Finds all instances of ‘Der’. Instead of SEARCH, this uses FIND, which is case-sensitive.

Scott Dermott

Example 3

Searches for all instances of ‘der*n’.

Sandra Henderson

Example 4

Searches for all instances of ‘o??s’.

Michael Thompson

Example 5

Searches for all instances of ‘co*~*’.

Ryan Coleman*
Nicole Young*