# Find returns #value! if search string not found. How to get around that?

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.

edit retag close merge delete

Sort by » oldest newest most voted

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.

more

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.

( 2016-10-02 01:34:31 +0100 )edit

@mrdue40: Please don't shout at us.
The answer by @ROSt52 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.

( 2016-10-02 11:34:37 +0100 )edit

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.

more

Dealing with FIND errors, depending on task solved, fits to patterns like these:

=IF(ISERROR(FIND(...));"Not found";"Found")


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.

more

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.

more

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.

more

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.

( 2016-10-02 01:50:23 +0100 )edit

@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

( 2016-10-02 11:09:11 +0100 )edit

@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.

( 2016-10-02 11:47:40 +0100 )edit

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.

more

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:

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

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

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

more

=IF(ISERROR(FIND(...));"Not found";"Found")

=IF(ISNUMBER(FIND(...));"Found";"Not found")

( 2019-02-12 06:14:03 +0100 )edit

Wow. Both of those work as well lol. A lot more simpler and more direct than my method I would have to say.

( 2019-02-13 02:46:41 +0100 )edit

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.

( 2019-02-13 08:23:04 +0100 )edit

## Stats

Seen: 13,683 times

Last updated: Feb 13