Finding text within a cell: the IFERROR function

By Neil Shorney, Director, Naturally Sales Ltd.


Many people are familiar with using VLOOKUP, INDEX/MATCH or similar lookup formulas to find cell values from a list. However, when we start trying to find a text string within a cell, or to see whether a cell contains certain text or not, things become more difficult.
Unfortunately, Excel doesn't give us a formula to ask it to find cells which contain certain text, along with other text, in the cell body. However, there are techniques we can use. This will be a 3-part e-tip as we address this useful topic. The first part is to learn the IFERROR function.

The IFERROR functions allows us to substitute an error value in a cell for something else of our choosing, and it works by wrapping around the formula you're creating which may (or may not) return an error value. If works as follows:

    =IFERROR(formula,value_if_error)


for example:

    =IFERROR(SUM(A1:A10),0)


...which will return the sum of A1:A10 unless that sum is an error value, in which case it will display 0 instead of the error.

Struggling to see how this will help you find certain text within a cell? Look out for part 2 coming in a fortnight's time as we take this a stage further.