Feb 18 2020 06:46 AM
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?
Feb 18 2020 07:12 AM - edited Feb 18 2020 07:13 AM
SolutionPerhaps the attached file helps you find a solution. It combines FILTER with a SEARCH for the text string in the larger text.
Feb 18 2020 07:15 AM
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.
Feb 18 2020 07:16 AM
Feb 18 2020 07:17 AM
Feb 18 2020 07:27 AM
If I also then want to exclude any that have the text "TBC" in them how would I add that to your existing formula?
Feb 18 2020 07:29 AM
@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)))
Feb 18 2020 07:34 AM
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.
Feb 18 2020 07:35 AM
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".
Feb 18 2020 07:44 AM
Feb 18 2020 07:53 AM
Feb 18 2020 07:58 AM
@StuLux23 Please see attached.
Feb 18 2020 08:03 AM
Excellent, thank you very much for your help and speed of replies.
My original workbook uses VBA to create filtered lists (it was created before the FILTER command was released). Do you think that using this new formula (instead of VBA) is a better approach.
I can see the advantage that it doesn't require the user to run any code and it is always up to date but I wondered whether adding loads of additional formulas to a file might make it unstable, what are your thoughts?
Feb 18 2020 08:17 AM
IMHO, one filter is enough for this formula
=FILTER($A$2:$A$8,ISNUMBER(SEARCH($C$1,$B$2:$B$8))*NOT(ISNUMBER(SEARCH("TBC",$B$2:$B$8))))
Feb 18 2020 08:53 AM
@Sergei Baklan Again, much neater!
Mar 02 2022 02:24 PM
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
Feb 18 2020 07:12 AM - edited Feb 18 2020 07:13 AM
SolutionPerhaps the attached file helps you find a solution. It combines FILTER with a SEARCH for the text string in the larger text.