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.

Use

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

--


Regards,


Peo Sjoblom

"DOUG01A" wrote in message
...
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.

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!

"Peo Sjoblom" wrote:

Use

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

--


Regards,


Peo Sjoblom

"DOUG01A" wrote in message
...
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.

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

"DOUG01A" wrote in message
...
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!

"Peo Sjoblom" wrote:

Use

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

--


Regards,


Peo Sjoblom

"DOUG01A" wrote in message
...
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.

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))


--
John C


"DOUG01A" wrote:

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.

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)

--
Biff
Microsoft Excel MVP


"DOUG01A" wrote in message
...
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.