FIND function does not return ZERO when text is not found
 

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.

FIND function does not return ZERO when text is not found
 

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.

FIND function does not return ZERO when text is not found
 

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.

FIND function does not return ZERO when text is not found
 

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.

FIND function does not return ZERO when text is not found
 

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.

FIND function does not return ZERO when text is not found
 

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.