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 𝗗𝗲𝗿mott
Sandra Hen𝗱𝗲𝗿son
Raymond van 𝗱𝗲𝗿 Hilch
Example 2
Finds all instances of ‘Der’. Instead of SEARCH, this uses FIND, which is case-sensitive.
Scott 𝗗𝗲𝗿mott
Example 3
Searches for all instances of ‘der*n’.
Sandra Hen𝗱𝗲𝗿so𝗻
Example 4
Searches for all instances of ‘o??s’.
Michael Th𝗼mp𝘀on
Example 5
Searches for all instances of ‘co*~*’.
Ryan 𝗖𝗼leman*
Ni𝗰𝗼le Young*
#excel #excelformulas #exceltips #excelskills #exceltricks #excelhacks #microsoftexcel #msexcel #microsoft365 #office365
