SOLVED
Home

Can I use wildcards with the new FILTER function

%3CLINGO-SUB%20id%3D%22lingo-sub-1179560%22%20slang%3D%22en-US%22%3ECan%20I%20use%20wildcards%20with%20the%20new%20FILTER%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1179560%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20be%20able%20to%20filter%20a%20list%20that%20contains%20cells%20with%20lengthy%20text%20strings%20in%20them%20such%20as%20%2212%3A15%20to%2012%3A35%20Meeting%20with%20(Sponsor%201)%20at%20table%20(2)%20(Delegate%20Request)%22.%26nbsp%3B%20I%20want%20to%20create%20a%20new%20list%20filtered%20specifically%20for%20an%20individual%20sponsor%20so%20I%20have%20tried%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DFILTER(D%3AD%2CV%3AV%3D%22*Sponsor%201%22)%3C%2FP%3E%3CP%3Ebut%20I%20get%20a%20%23CALC!%20error%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20use%20wildcards%20with%20this%20function%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1179560%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1179625%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20use%20wildcards%20with%20the%20new%20FILTER%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1179625%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F562010%22%20target%3D%22_blank%22%3E%40StuLux23%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerhaps%20the%20attached%20file%20helps%20you%20find%20a%20solution.%20It%20combines%20FILTER%20with%20a%20SEARCH%20for%20the%20text%20string%20in%20the%20larger%20text.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1179641%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20use%20wildcards%20with%20the%20new%20FILTER%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1179641%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F562010%22%20target%3D%22_blank%22%3E%40StuLux23%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20doesn't%20appear%20wildcards%20are%20supported%20with%20FILTER%20(Maybe%20added%20at%20a%20later%20date%3F).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20workaround%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Patrick2788_0-1582038903190.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F171699iFC52A7A21E3EA87E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Patrick2788_0-1582038903190.png%22%20alt%3D%22Patrick2788_0-1582038903190.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E*Can%20also%20use%20SEARCH%20instead%20of%20FIND%20when%20case%20does%20not%20matter.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1179647%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20use%20wildcards%20with%20the%20new%20FILTER%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1179647%22%20slang%3D%22en-US%22%3EYes%20-%20that's%20great%20thanks%2C%20works%20just%20as%20expected.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1179650%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20use%20wildcards%20with%20the%20new%20FILTER%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1179650%22%20slang%3D%22en-US%22%3EThank%20you%2C%20this%20also%20looks%20like%20it%20would%20work%20well.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1179678%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20use%20wildcards%20with%20the%20new%20FILTER%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1179678%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F562010%22%20target%3D%22_blank%22%3E%40StuLux23%3C%2FA%3E%26nbsp%3BThanks!%20But%2C%20I%20must%20admit%20that%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3E%26nbsp%3B's%20solution%20is%20neater.%20No%20need%20for%20the%20IFERROR%20part%20in%20mine.%20I'd%20prefer%20ISNUMBER.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20in%20my%20file%20would%20then%20become%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DFILTER(%24A%242%3A%24A%248%2CISNUMBER(SEARCH(%24C%241%2C%24A%242%3A%24A%248%2C1)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1179673%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20use%20wildcards%20with%20the%20new%20FILTER%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1179673%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20I%20also%20then%20want%20to%20exclude%20any%20that%20have%20the%20text%20%22TBC%22%20in%20them%20how%20would%20I%20add%20that%20to%20your%20existing%20formula%3F%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1179708%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20use%20wildcards%20with%20the%20new%20FILTER%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1179708%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F562010%22%20target%3D%22_blank%22%3E%40StuLux23%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20other%20question%20crossed%20my%20previous%20reply.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DFILTER(FILTER(%24A%242%3A%24A%248%2CISNUMBER(SEARCH(%24C%241%2C%24A%242%3A%24A%248%2C1)))%2CNOT(ISNUMBER(SEARCH(%22TBC%22%2CFILTER(%24A%242%3A%24A%248%2CISNUMBER(SEARCH(%24C%241%2C%24A%242%3A%24A%248%2C1)))%2C1))))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThe%20formula%20above%20would%20Find%20%22Sponsor%201%22%20(in%20C1)%20but%20would%20exclude%20%22TBC%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1179742%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20use%20wildcards%20with%20the%20new%20FILTER%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1179742%22%20slang%3D%22en-US%22%3EI'm%20getting%20a%20%23VALUE!%20error%20with%20this%20formula%2C%20some%20of%20the%20entries%20have%20both%20%22Sponsor%201%22%20and%20%22TBC%22%20in%20the%20text%20string%20but%20I%20only%20want%20to%20return%20those%20where%20they%20have%20%22Sponsor%201%22%20but%20NOT%20%22TBC%22%20which%20is%20what%20your%20formula%20appears%20to%20be%20trying%20to%20do%20but%20I%20can't%20get%20it%20to%20work%20with%20my%20data%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1179767%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20use%20wildcards%20with%20the%20new%20FILTER%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1179767%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20attached%20a%20working%20example%20but%20this%20is%20giving%20a%20%23Calc!%20error%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1179778%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20use%20wildcards%20with%20the%20new%20FILTER%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1179778%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F562010%22%20target%3D%22_blank%22%3E%40StuLux23%3C%2FA%3E%26nbsp%3BPlease%20see%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1179781%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20use%20wildcards%20with%20the%20new%20FILTER%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1179781%22%20slang%3D%22en-US%22%3E%3CP%3EExcellent%2C%20thank%20you%20very%20much%20for%20your%20help%20and%20speed%20of%20replies.%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20original%20workbook%20uses%20VBA%20to%20create%20filtered%20lists%20(it%20was%20created%20before%20the%20FILTER%20command%20was%20released).%26nbsp%3B%20Do%20you%20think%20that%20using%20this%20new%20formula%20(instead%20of%20VBA)%20is%20a%20better%20approach.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20see%20the%20advantage%20that%20it%20doesn't%20require%20the%20user%20to%20run%20any%20code%20and%20it%20is%20always%20up%20to%20date%20but%20I%20wondered%20whether%20adding%20loads%20of%20additional%20formulas%20to%20a%20file%20might%20make%20it%20unstable%2C%20what%20are%20your%20thoughts%3F%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1179700%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20use%20wildcards%20with%20the%20new%20FILTER%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1179700%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20right%20it%20is%20a%20little%20neater%20(i.e.%20shorter)%20and%20reads%20easier.%26nbsp%3B%20I'm%20trying%20to%20add%20an%20additional%20criteria%20to%20exclude%20any%20in%20the%20list%20that%20have%20the%20text%20%22(TBC)%22%20in%20them%20but%20struggling%20to%20add%20this%20to%20the%20existing%20formula.%26nbsp%3B%20I've%20read%20about%20using%20*%20to%20add%20more%20criteria%20but%20can%3Bt%20get%20the%20syntax%20right%20to%20include%20both%20arguments.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1179831%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20use%20wildcards%20with%20the%20new%20FILTER%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1179831%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIMHO%2C%20one%20filter%20is%20enough%20for%20this%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DFILTER(%24A%242%3A%24A%248%2CISNUMBER(SEARCH(%24C%241%2C%24B%242%3A%24B%248))*NOT(ISNUMBER(SEARCH(%22TBC%22%2C%24B%242%3A%24B%248))))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1179986%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20use%20wildcards%20with%20the%20new%20FILTER%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1179986%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BAgain%2C%20much%20neater!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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? 

14 Replies
Highlighted
Solution

@StuLux23 

Perhaps the attached file helps you find a solution. It combines FILTER with a SEARCH for the text string in the larger text.

 

Highlighted

@StuLux23 

It doesn't appear wildcards are supported with FILTER (Maybe added at a later date?).

 

The workaround:

Patrick2788_0-1582038903190.png

*Can also use SEARCH instead of FIND when case does not matter.

Highlighted
Yes - that's great thanks, works just as expected.
Highlighted
Thank you, this also looks like it would work well.
Highlighted

If I also then want to exclude any that have the text "TBC" in them how would I add that to your existing formula?

@Riny_van_Eekelen 

Highlighted

@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)))

 

Highlighted

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.

 

@Riny_van_Eekelen 

Highlighted

@StuLux23 

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".

Highlighted
I'm getting a #VALUE! error with this formula, some of the entries have both "Sponsor 1" and "TBC" in the text string but I only want to return those where they have "Sponsor 1" but NOT "TBC" which is what your formula appears to be trying to do but I can't get it to work with my data?
Highlighted

I've attached a working example but this is giving a #Calc! error?

 

@Riny_van_Eekelen 

Highlighted

@StuLux23 Please see attached.

Highlighted

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?

@Riny_van_Eekelen 

Highlighted

@Riny_van_Eekelen 

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))))

 

Highlighted