When using either FIND(“FindText”; “Text”; Position) or SEARCH(“FindText”; “Text”; Position), if the FindText is within the Text, it returns the position where it is found. But if the FindText is not within the Text, it returns #VALUE!. Shouldn’t it return 0? How to get around this problem? Thanks.
Returning “#VALUE!” is correct because there is no value to be displayed. “0” would be a value indicating a position before “1”.
Maybe you can use the FIND function within the IF function. Depends on your needs.
if you will excuse me.
returning no value should return a NULL value.
#VALUE! is an error condition, not finding the string is not an error condition.
@mrdue40: Please don’t shout at us.
The answer by @ROSt53 is correct. FIND is specified to return a number indicating the position if an occurrence is found and an error valueotherwise. This mainly to ensure interoperabiity. General purpose programming languages also supply functions with the basic functionality of FIND and mostly return 0 (numerical, not NULL!) in case of faiure leaving the conclusions to the programmer. The information returned is the same.
Returning an error is what Calc does, but it is NOT “correct”. Isn’t there a bug/feature request filed somewhere for enabling simple searching without the ridiculous =IF(ISERROR(FIND(... ?
That’s surely neither ridiculous nor incorrect. In one or another way the evaluator has to signal the fact that FIND did not succeed with finding.
However, since the release of V4.0 on 2013-02-01 there are the functions IFERROE() and IFNA() available made to allow for a more efficient handling of errors.
You can look for bug reports and feature requests in bugs.documentfoundation.org if there comes up another “ridiculous” problem.
I think IFERROR() function can help.
=IF(IFERROR(FIND(“R”,A1),0),“YES”,“NO”)
Maybe you can simplify a bit, visualizing Yes/No while the cell has the position or zero if it is not found.
Spreadsheet applications deal with “not found” conditions in FIND at al using their versatile result-checking functions:
Dealing with FIND errors, depending on task solved, fits to patterns like these:
=IF(ISERROR(FIND(...));"Not found";"Found")
=IF(ISNUMBER(FIND(...));"Found";"Not found")
=IFERROR(FIND(...);"Not Found Value")
They effectively cover all possible use cases, using the paradigm native to the spreadsheet applications. Trying to work against the system, and ask for ways to have some numeric “error” result from FIND (other than the third formula above) is much alike using a paradigm in a programming language built around a different paradigm (like trying to use OOP when programming in erlang).
Returning any number as indicator for “not found” condition is bad in a spreadsheet application. It is not a programming language; it is intended to be used by ordinary users not trained in programming logic.
Using a numerical returned value in further calculations without noticing that the result is not a position, but a special indicator, may result in a severe errors in the calculations. Especially when the functions are used not solely, but inside a complex formula. Returning an error (a specific error indicating the “No result”!) makes it impossible to overlook the problem.
If user/spreadsheet author becomes aware that the search may fail (seeing the error), the user would further modify the formulas to handle the situation correctly. In most cases, the handling of the situation is very different from normal handling of found position, and thus this is an exception in a sense - an additional argument for returning an error. LibreOffice includes special functions to handle this: ISERROR/IFERROR. They are very clear in syntax (especially the latter), and make the intent very clear, unlike using IF with comparison with some arbitrary constant. The IFERROR has the benefit of allowing to only calculate the position once in the formula, without the need to store the result in a separate cell. Hypothetical use of IF would require to calculate position twice:
=IF([find_formula] > 0; [something_including_find_formula_again]; [not_found_handling])
compared to
=IFERROR([something_including_find_formula]; [not_found_handling])
Finally, returning an error gives yet another benefit. It has a property of propagating through levels of calculations; thus it’s possible to handle the situation not immediately in the place of happening, but in some other level of nesting, where it makes more sense. E.g., something like
=IFERROR(TEXT(A1; "0.00"" " & RIGHT(B1; LEN(B1)-FIND("CUR: ";B1)-4) & """"); "BAD B1!")
Without the existing property of FIND returning #VALUE! when not found, the formula would be much longer and more cryptic for the same result.
And one final note: in Calc, unlike low-level programming languages, returning an error does not impose any overhead compared to returning a number. So, no performance penalty here.
I was using it in an IF function. =IF(FIND(“R”,A1),“YES”,“NO”). Produces YES if R is anywhere in cell A1 and #VALUE! if it isn’t. Makes for a very ugly spreadsheet. If it did yield a 0, how can there be a position before “1”? It shouldn’t be an error. I tried trapping it with a cell that was =ERRORTYPE(B1) which showed 519 when the #VALUE! showed up, but yielded “#N/A” when there was an “R” in cell A1. Stymied again.
iferror( find (“not”,“here”),"")
will return “”.
I think that find (“not”,“here”),"") should return NULL or the empty string or -1 or something computable.
It should NOT return an error condition. it makes calc unusable for the simplest tasks you might expect from a spreadsheet.
Using iferror is a lot of bloatcode, and returning #VALUE!
is returning an error condition. that is flawed logic, because searching for a string that happens not to be there, is not an error condition.
find should simply return a NULL value if there is no value to return. That would be a lot more helpful and consistent with e.g. common standards in the SQL world.
find() is also not consistent with e.g. the IF() function.
IF(“not”=“here”,“ok”) will never return an error, altough we are doing the same comparison here as in the
find() example. IF searches if “not” matches “here”, but does not return a #VALUE! if that is not true.
@mrdue40:Using FIND we are asking at what position (if any) the saerch string is starting to occur in full length within the target string. If such an occurrence not is found the error-value #VALUE! is returned.
The above mentioned expression using IF only tests if the first string is identical to the the second string. This relation is reflexive. (The given example is incomplete. ElseExpression missing.)
FIND and IF based on a string equality are next to completely unrelated
@mrdue40: Using IFERROR for the purpose it’s up to you to define what result will be returned in case of FIND failing to find a position. You cannot return NULL, however, because there is no supported formula result type containing NULL (which would be of a pointer type in general programming) and no supexpression producing it. Concerning our mental associations with NULL the error value returned by the NA() function should apply.
I have found a ‘dirty’ way of getting around the problem. Just add the “Find text” to the end of “Text” that is “Text”&“Find text” eg =FIND(“Find text”,“Text”&“Find text”,0) . BUT depending on usage, you will need to add other functions to get the desired result.
I’ve come up with a workaround for this problem.
My issue was that I needed to be able to specify a condition if the FIND() function CAN find the string I am looking for. The IFERROR() function does not leave you an option to do so; it only allows you to specify what happens if the evaluation returns an error. If the calculation is not an error, the formula puts the result of the calculation in the cell. If it is an error, the formula puts what you tell it to. I needed a way to put something different in the cell if the calculation did evaluate to true, not just the calculation itself. The solution is to nest an IFERROR(FIND()) function inside of an IF() function as so:
=IF(IFERROR(FIND(“String”,A:A),0)>0,Result if “String” is found, Result if “String” is not found)
What this formula is doing is first checking to see if it can find “String” in the A column per the cell’s row (A:A means search in the cell that is in the same row as this cell but in column A). If the formula does find “String” it returns the position that “String” starts in the full text as a number (if “String” was the very first word, a 1 would be returned to state that “String” starts at position 1). Let’s assume that “String” was at the beginning of the text. The FIND() formula returns a 1. Because 1 is not an error, the formula then moves to the IF() condition. It will put the 1 in place of the IFERROR(FIND(“String”,A:A,) part of the formula. Now your IF() statement reads
=IF(1>0,Result if “String” is found, Result if “String” is not found)
This is a valid statement and Result if “String” is found will be the value put in the cell because 1>0 is true.
On the other hand, if the FIND() formula returns an error, the IFERROR() function will catch that and return a 0 as I requested. At that point, your statement reads
=IF(0>0,Result if “String” is found, Result if “String” is not found)
which we know is not true so Result if “String” is not found is put in the cell.
I hope this helps some people! I just resolved this issue myself and this solution is full-proof for me as of right now.
Also, toward the debate of whether the way in which the FIND() function returns an Excel #error is valid or not:
My opinion is that the way it currently works is valid but it is limiting to the user and there should be a simple solution to the problem. Offering a version of string searching to the non-programming user that returns an Excel error if the string can’t be found is understandable even though it doesn’t entirely match the logic behind most other #error values. What Microsoft should do is also create a function that behaves the way a programmer would expect; a boolean returning the value if it’s true and a set value if it’s not. For us who are computer language savvy, the set value if a string search is false is -1. The function could be called =FINDB() (B for boolean) with the exact same arguements. Fortunately, it doesn’t matter if they do or not because the logic I used above to resolve the issue will stand the test of time (as long as they don’t change the behavior of any of those functions).
=IF(ISERROR(FIND(...));"Not found";"Found")
=IF(ISNUMBER(FIND(...));"Found";"Not found")
Wow. Both of those work as well lol. A lot more simpler and more direct than my method I would have to say.
It’s just that people are accustomed of thinking about some problem some single way, and spend time working around to bring that way to another environment, instead of familiarizing themselves with the environment. Much like using OOP paradigm in erlang, or signal processing using VBS. Calc solves the problem in its own way, and does that nicely; but of course, trying to work against the system gives ugly solutions and topics like this one.
I feel pity that the system which should be easy to use for ordinary people, is becoming more and more complex, because the developers want absolutely perfect behavior. Of course “find” should return 0 if it does not find the word, working in this case with “iferror” functions makes working much more difficult. Simple problems should be solved by simple solutions. We are living with AI development time and still we do not have good tools for ordinary people to make software applications.