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.
Searches for all instances of ‘der’ (see previous images).
Raymond van 𝗱𝗲𝗿 Hilch
Finds all instances of ‘Der’. Instead of SEARCH, this uses FIND, which is case-sensitive.
Searches for all instances of ‘der*n’.
Searches for all instances of ‘o??s’.
Searches for all instances of ‘co*~*’.