FILTER with wildcards

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

What are the wildcard characters?

* (asterisk) — represents any number of characters.

? (question mark) — represents a single character.

~ (tilde) — indicates the next character is literal. Used to find an asterisk, question mark, or tilde.

One workaround is to use FILTER in conjunction with ISNUMBER and SEARCH in its 𝚒𝚗𝚌𝚕𝚞𝚍𝚎 argument.

SEARCH returns the first character position of each string in the range. If it fails to find it, a #VALUE error is produced.

{VALUE!;VALUE!;VALUE!;VALUE!;7;11;VALUE!;VALUE!;VALUE!;13;VALUE!}

As the array contains either a number or an error, when it’s fed into ISNUMBER, another array of TRUE/FALSE values is generated. This determines the rows returned by FILTER.

{FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE}

Formula evaluations are based on Example 1.

Example 1

Searches for all instances of ‘der’.

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*