A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

FIND function does not return ZERO when text is not found



 
 
Thread Tools Display Modes
  #1  
Old August 5th, 2008, 05:22 PM posted to microsoft.public.excel.worksheet.functions
DOUG01A
external usenet poster
 
Posts: 2
Default 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.
  #2  
Old August 5th, 2008, 05:33 PM posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom[_2_]
external usenet poster
 
Posts: 963
Default 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.



  #3  
Old August 5th, 2008, 05:37 PM posted to microsoft.public.excel.worksheet.functions
John C[_2_]
external usenet poster
 
Posts: 1,350
Default 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.

  #4  
Old August 5th, 2008, 05:38 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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.



  #5  
Old August 5th, 2008, 05:45 PM posted to microsoft.public.excel.worksheet.functions
DOUG01A
external usenet poster
 
Posts: 2
Default 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.




  #6  
Old August 5th, 2008, 05:56 PM posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom[_2_]
external usenet poster
 
Posts: 963
Default 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.






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:31 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.