How do I make a "contain" formula in Excel?

There a simple way to create a "contains" formula in Excel. It works the same way as the AutoFilter feature 'Contains'.

For one cell, you could use the SEARCH formula that returns the position on a string where you can find another string or an error. With some logical formulas you can make Excel return the value if it is present or not. Example:

=NOT(ISERROR(SEARCH("x",A1)))

In order to count in a range, you could use the SUMPRODUCT formula. This will return the number of cells that contains the "x" text in the A1:A10 range. Something like this:

=SUMPRODUCT(--NOT(ISERROR(SEARCH("x",A1:A10))))


This formula, which is a little bit of a longer formula, returns whether it contains ABCD.

=IF(ISERROR(FIND("ABCD",A1)),"False","True")

Or there is also this simpler formula, but will return TRUE if the cell DOES NOT contain ABCD

=ISERROR(FIND("ABCD",A1))

=FIND("what you are looking for", cell ) -- the output is the number of characters from the beginning of the cells value where it finds "what you are looking for". If it doesn't find it, it returns #Value.

=ISERROR(cell) -- output is whether that cell returns an error (such as #Value)

=IF(ISNUMBER(SEARCH(Exceptions!$J$2,Sheet2!A2)), "DELETE","")