Formula for the "contains" logic in Data Filter titles?

Posted by Leslie on December 06, 2001 10:50 AM

Is there a way to code the "contains" function accessible via Data, Filter, Autofilter titles for use in an IF statement? I need to code a function to categorize a column of text strings: Category A if the value contains text1, B if it contains text2, C if it contains BOTH text1 AND text2, etc.

I tried to use MATCH and "*text1*" in chained IF statements but got hung up in handing the way MATCH returns "N/A" for "not present" ...

Apologies if this is a stupid question but I've never had to do this before. Thanks!

Posted by Mark W. on December 06, 2001 10:56 AM

{=CHOOSE(SUM(ISNUMBER(MATCH({"*text1*","*text2*"},A2,0))*{1,2})+1,"","A","B","C")}

Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.

Posted by Leslie on December 06, 2001 11:15 AM

I bow to genius. Thank you! ~Leslie