Suppressing #Value! when using Find function

  • Thread starter Thread starter Ralph Heidecke
  • Start date Start date
R

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
 
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)))
 
Hi Ralph,

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

Hope that helps.

Regards,
Kevin
 
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?
Click to expand...

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.
 
Back
Top