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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|