02-18-2020 06:46 AM
02-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:
but I get a #CALC! error
Is it possible to use wildcards with this function?
02-18-2020 07:12 AM - edited 02-18-2020 07:13 AMSolution
Perhaps the attached file helps you find a solution. It combines FILTER with a SEARCH for the text string in the larger text.
02-18-2020 07:15 AM
It doesn't appear wildcards are supported with FILTER (Maybe added at a later date?).
*Can also use SEARCH instead of FIND when case does not matter.
02-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?
02-18-2020 07:29 AM
02-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.
02-18-2020 07:35 AM
Your other question crossed my previous reply.
The formula above would Find "Sponsor 1" (in C1) but would exclude "TBC".
02-18-2020 07:44 AM
02-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?
02-18-2020 08:17 AM
IMHO, one filter is enough for this formula