Suppressing #Value! when using Find function

[Ralph Heidecke]

I have a number of large (couple of thousand lines) worksheetsI need to test
if the the string "06" is in the cells in column A. For example:

If the value in A1 is "SCO3 0600-1415/0600-1815" if I use the formula
=FIND("06",A1,1) returns 23.

However when the cell the formula is applied to doesn't contain "06" as in:

A2 = "DSS Delta 1 1415-2230" the formula
returns #VALUE!

is there a way rewrite =FIND("06,A2,1) so that if "06" is not found the
value returned is either blank or 0?

Thanks

 

[Peo Sjoblom]

Try

=ISNUMBER(FIND("06",A1,1))

returns true if found and false if not, otherwise you can use

=IF(ISNUMBER(FIND("06",A1,1)),1,0)

that will return 1 for found and zero if not, if you want to count a whole
range

=SUMPRODUCT(--ISNUMBER(FIND("06",A1:A50,1)))

 

[Dave R.]

use:
if(iserror(find(xxxxxx,xx,x),"",find(xxxxxx,xx,x))

 

[Kevin Stecyk]

Hi Ralph,

=IF(ISERROR(FIND("06",A2,1)), "", FIND("06",A2,1))

Hope that helps.

Regards,
Kevin

 

[Harlan Grove]

I have a number of large (couple of thousand lines) worksheetsI need to test
if the the string "06" is in the cells in column A. For example:

If the value in A1 is "SCO3 0600-1415/0600-1815" if I use the formula
=FIND("06",A1,1) returns 23.

However when the cell the formula is applied to doesn't contain "06" as in:

A2 = "DSS Delta 1 1415-2230" the formula
returns #VALUE!

is there a way rewrite =FIND("06,A2,1) so that if "06" is not found the
value returned is either blank or 0?

Just another alternative that needs only two nested function call levels.

=MOD(FIND("06",A2&"06"),LEN(A2)+1)

This returns the character position of "06" in A2 if found or 0 otherwise.