When using the find function to find text within a string, failure to find any of that text is not an error or an unexpected results and should be handled gracefully with a return of 0, as a logical progression of a positive result being the location within the string of the searched text. Currently the failure to find any of the text results in the error code #VALUE! which then breaks successive functions and thus renders the find function unusable. To reproduce enter the following in the cells as per column header: A1 B1 C1 test =find("e",A1) =find("d".A1) Result is as expected for B1, the location in the text string "test" of "e" i.e. 2 Result expected for C1 would be 0 or nothing (not found) but instead is error #VALUE! which cannot be handled gracefully in successive formulas.
the formula =IF(ISERROR(FIND(searchstring;text));0;FIND(searchstring;text)) gives the expected result.
This is not a bug.It is the expected behavior in odf 1.2
For those who, like me, coouldn't understand why 0 isn't a logical outcome for "not found" in a find function, I asked an expert in this sort of thing (spreadsheet developer and ODF TC ;) ). It's that damned legacy thing again... However there is a workaround using another function called ISERR. So if like me you need FIND to return an integer or otherwise testable answer rather than an error, wrap it in a ISERR: IF(ISERR(FIND("my string",<the cell to search>),0,FIND("my string",<the cell to search>))
(In reply to comment #3) > For those who, like me, coouldn't understand why 0 isn't a logical outcome for > "not found" in a find function, I asked an expert in this sort of thing > (spreadsheet developer and ODF TC ;) ). It's that damned legacy thing again... > > However there is a workaround using another function called ISERR. So if like > me you need FIND to return an integer or otherwise testable answer rather than > an error, wrap it in a ISERR: > > IF(ISERR(FIND("my string",<the cell to search>),0,FIND("my string",<the cell to > search>)) Or even =IF(ISERR(FIND("my string",<the cell to search>)),0,FIND("my string",<the cell to search))
I've just reported the same bug and was pointed here. I fully agree that the current behaviour is pretty much nonsense and makes coding so much more complex. In Excel this is way easier IIRC. I wonder how working code from an Excel sheet using the find() function looks like after import? Is it mangled in functionality? Do we need to fix it? I still subscribe to the idea of outputting 0 gracefully rather than an error that needs way more complex handling. This is NOT efficient. 0 would correspond the value of FALSE and thus stay compatible? Where do I have to file this as a feature wish/proposal? I used it a lot in Excel and now I have to rethink a LOT of code when trying to solve the same in LibreOffice.