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:
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:
This formula, which is a little bit of a longer formula, returns whether it contains ABCD.
Or there is also this simpler formula, but will return TRUE if the cell DOES NOT contain ABCD
=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)