Is it possible to check if two collumns contain a certain text/string using filters in excel?

Question

I have a database and I would like to display all of the rows that have the word "sales" CONTAINED in column B OR D.

I tried using advanced filters, but I was only able to show rows that had cells EQUAL to "sales" in B or D (e.g. it excluded cells containing things like "the sales").

Using the regular filter function I can only do contained in one column or contained in both columns, there is not an option that I could find checks if it is contained in EITHER column.

It may be worth noting that I was also unable to incorporate =ISNUMBER(SEARCH(substring,text)) into a condition for advanced filtering, but that is a way to check if a cell contains a certain text.

Thanks in advance!

Answer 1

One solution I just thought of involves creating 3 new column E,F,G that that check if column B, column D, or either contains "sales", respectively:

Column E: =ISNUMBER(SEARCH("Sales",B8)) Column F: =ISNUMBER(SEARCH("Sales",D8)) Column G: =OR(E8=TRUE, F8=TRUE)

Then Filter by column G all that are TRUE. This is very much a workaround and not very easy to use so if you guys have other suggestions please let me know!

Answer 2

Another solution I came up with involves making just one extra column and making it contain all of the text in the desired columns through =B8&D8 , then filtering through this column.