Bug Hunting Session
Bug 38430 - The find function returns a error code instead of 0
Summary: The find function returns a error code instead of 0
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.3.2 release
Hardware: All All
: medium major
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-06-18 01:47 UTC by putt1ck
Modified: 2012-06-18 14:06 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description putt1ck 2011-06-18 01:47:27 UTC
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.
Comment 1 wope 2011-06-18 10:23:22 UTC
the formula
=IF(ISERROR(FIND(searchstring;text));0;FIND(searchstring;text))
gives the expected result.
Comment 2 wope 2011-06-18 11:33:23 UTC
This is not a bug.It is the expected behavior in odf 1.2
Comment 3 putt1ck 2011-06-19 22:13:41 UTC
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>))
Comment 4 putt1ck 2011-06-19 23:29:11 UTC
(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))
Comment 5 Axel Mertes 2012-06-18 14:06:03 UTC
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.