Using OR with the CONTAINS function (-or- How to Hack the CONTAINS Function)

Lucas Rayala

This one comes up a lot, so I thought I would provide a few ways to solve it. The question is this: can I use the OR function in the CONTAINS function? For instances, you want to see if a cell contains "apple", "banana", or "orange".

In short, you can't use OR in the CONTAINS function (TLDR: because OR provides binary true/false outputs, so it doesn't make sense to put variables inside it).

But let me provide two solutions:

1.) Wrapping multiple CONTAINS functions within the OR functions. Yes, this is what many people are trying to avoid, but it does the job. This is formatted like this:

=IF(OR(CONTAINS("apple", column@row), CONTAINS("banana", column@row), CONTAINS("orange", column@row)), "Fruit!", "No Fruit!")

Now the OR function is searching for instances where any of the CONTAINS statements are true, meaning if any of the fruit names are inside the referenced cell.

2.) Hack! Reverse the polarity (this is my favorite) -- you can sometimes flip the "search for" and "search within" portions of the CONTAINS function like this:

=IF(CONTAINS(column@row, "apple banana orange"), "Fruit!", "No Fruit!")

Now your IF statement is looking for IF the entry in column@row is contained in the string "apple banana orange". Is the value in column@row "apple"? Great! "Fruit!"

This only works if column@row contains the actual fruit names. It wouldn't work if it contained a statement like "I like to eat apples", because that statement doesn't exist in the string "apple banana orange". But there are a ton of uses for this flipped version of the CONTAINS function, and when it works, it's a lot tidier. I use it often to exclude certain items from lists that are thousands of lines long. For instance, if I have a list of lot numbers I need to reference, but I know that 5 of 10,000 are bad, I can exclude them with a formula like this:

=IF(CONTAINS(lotnumber@row, "AX3432 AC3924 AF4030 AQ2020"), "NA", lotnumber@row)

This format makes it really easy for me to add to the exclusion list over time -- I just stick them in the list.

If you have fancy shifting variables you want to look for, you can replace the string with a JOIN COLLECT combination function. For instance:

=IF(CONTAINS(lotnumber@row, JOIN(COLLECT({bad lots}, {lot signifier}, "bad"), " ")), "NA", lotnumber@row)

The above example could be accomplished via other means, but this is just to show that the contains function is pretty flexible.

I hope this is helpful!

kristie_sanchez2

How about CONTAINS with AND?