FIND function does not return ZERO when text is not found

[DOUG01A]

Why does the FIND("search text", "within text"[,start position]) function
return "#VALUE" instead of ZERO (0) when the search text is not in the
'within' string? #VALUE does not make sense as a result. If the text exists
the result will be its position which can not be less than 1. If it doesn't
exist then Zero indicates no position. Further How can I get a ZERO result
from FIND()? I am using Excel 2003.
The INSTR('find-text','within-text','start') function used to return Zero
but the function is not available in 2003.

 

[Peo Sjoblom]

Use

=IF(ISNUMBER(FIND("x",A1)),FIND("x",A1),0)

--


Regards,


Peo Sjoblom

 

[John C]

Some people, per chance, might be doing calculations based on the result of a
FIND. The error result if 0 forces the user to handle the error in another
method. Otherwise, the user may not realize exactly where the error is if
he/she is doing calculations and they are getting funny results.

Just my 2 cents.
Assuming you are looking for the character in A1, in the cell A2

=IF(ISERROR(FIND(A1,A2)),0,FIND(A1,A2))

 

[T. Valko]

You have to test for an error or number like this:

=IF(ISERR(FIND("x",a1)),0,FIND("x",A1))

=IF(COUNT(FIND("x",A1)),FIND("x",A1),0)

 

[DOUG01A]

Thank you Peo,
The ISNUMBER() check will give me the ZERO result as I requested. I only
wonder what Microsoft was thinking(?) when they decided to evaluate the FIND
function without giving a real answer. Instead of simply saying ZERO there is
no answer given and "#VALUE" indicates an erroneous format to me.

Thanks again!

Use

=IF(ISNUMBER(FIND("x",A1)),FIND("x",A1),0)

--


Regards,


Peo Sjoblom

Why does the FIND("search text", "within text"[,start position]) function
return "#VALUE" instead of ZERO (0) when the search text is not in the
'within' string? #VALUE does not make sense as a result. If the text
exists
the result will be its position which can not be less than 1. If it
doesn't
exist then Zero indicates no position. Further How can I get a ZERO result
from FIND()? I am using Excel 2003.
The INSTR('find-text','within-text','start') function used to return Zero
but the function is not available in 2003.

 

[Peo Sjoblom]

At least it shouldn't been a value error, #N/A would have been more fitting
if there should be an error just the same way a VLOOKUP returns that error
if it can't find
the lookup value.

--


Regards,


Peo Sjoblom

Thank you Peo,
The ISNUMBER() check will give me the ZERO result as I requested. I only
wonder what Microsoft was thinking(?) when they decided to evaluate the
FIND
function without giving a real answer. Instead of simply saying ZERO there
is
no answer given and "#VALUE" indicates an erroneous format to me.

Thanks again!

Use

=IF(ISNUMBER(FIND("x",A1)),FIND("x",A1),0)

--


Regards,


Peo Sjoblom

Why does the FIND("search text", "within text"[,start position])
function
return "#VALUE" instead of ZERO (0) when the search text is not in the
'within' string? #VALUE does not make sense as a result. If the text
exists
the result will be its position which can not be less than 1. If it
doesn't
exist then Zero indicates no position. Further How can I get a ZERO
result
from FIND()? I am using Excel 2003.
The INSTR('find-text','within-text','start') function used to return
Zero
but the function is not available in 2003.