SOLVED

Can I use wildcards with the new FILTER function

Copper 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? 

16 Replies
best response confirmed by StuLux23 (Copper Contributor)
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.

 

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

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

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 

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

 

@Riny_van_Eekelen 

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

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?

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

 

@Riny_van_Eekelen 

@StuLux23 Please see attached.

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 

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

 

@Sergei Baklan Again, much neater!

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

1 best response

Accepted Solutions
best response confirmed by StuLux23 (Copper Contributor)
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.

 

View solution in original post