Forum Discussion
Can I use wildcards with the new FILTER function
I want to be able to filter a list that contains cells with lengthy text strings in them such as "12:15 to 12:35 Meeting with (Sponsor 1) at table (2) (Delegate Request)". I want to create a new list filtered specifically for an individual sponsor so I have tried:
=FILTER(D:D,V:V="*Sponsor 1")
but I get a #CALC! error
Is it possible to use wildcards with this function?
Perhaps the attached file helps you find a solution. It combines FILTER with a SEARCH for the text string in the larger text.
19 Replies
If you want to search with multiple criteria using a * as a wildcard you can use the LET function with the SEARCH to define an array you can then FILTER. It's a bit messy but it works
It doesn't appear wildcards are supported with FILTER (Maybe added at a later date?).
The workaround:
*Can also use SEARCH instead of FIND when case does not matter.
- Thank you, this also looks like it would work well.
Perhaps the attached file helps you find a solution. It combines FILTER with a SEARCH for the text string in the larger text.
Thank you
Since February 2022, there is a solution for filtering and using wildcards.
Look here:
https://hermann-baum.de/excel/hbSort/en/xlookup.php#XVW2fi
Greetings
Hermann
If I also then want to exclude any that have the text "TBC" in them how would I add that to your existing formula?
Your other question crossed my previous reply.
=FILTER(FILTER($A$2:$A$8,ISNUMBER(SEARCH($C$1,$A$2:$A$8,1))),NOT(ISNUMBER(SEARCH("TBC",FILTER($A$2:$A$8,ISNUMBER(SEARCH($C$1,$A$2:$A$8,1))),1))))The formula above would Find "Sponsor 1" (in C1) but would exclude "TBC".
- Yes - that's great thanks, works just as expected.
StuLux23 Thanks! But, I must admit that Patrick2788 's solution is neater. No need for the IFERROR part in mine. I'd prefer ISNUMBER.
The formula in my file would then become:
=FILTER($A$2:$A$8,ISNUMBER(SEARCH($C$1,$A$2:$A$8,1)))
