Ads

Wednesday, September 2, 2009

Excel "Contains" Formula

There a simply way to use the "contains" formula in excel. It works the same as the AutoFilter 'Contains' in words.

For one cell, you can 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 if the value is present or not, for example:
=NOT(ISERROR(SEARCH("x",A1)))

In order to count in a range, you could use the SUMPRODUCT formula, something like:
=SUMPRODUCT(--NOT(ISERROR(SEARCH("x",A1:A10))))
This will return the number of cells that contains the "x" text in the A1:A10 range.

This returns whether it contains ABCD (but is a little bit of a longer formula)
=IF(ISERR(FIND("ABCD",A1)),"False","Tr…

or... the simpler formula, but will return TRUE if the cell DOES NOT contain ABCD
=iserr(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

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

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

No comments: