Forum Discussion
Can I use wildcards with the new FILTER function
Perhaps the attached file helps you find a solution. It combines FILTER with a SEARCH for the text string in the larger text.
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)))You're right it is a little neater (i.e. shorter) and reads easier. I'm trying to add an additional criteria to exclude any in the list that have the text "(TBC)" in them but struggling to add this to the existing formula. I've read about using * to add more criteria but can;t get the syntax right to include both arguments.
