Apache OpenOffice (AOO) Bugzilla – Issue 66590
Calc: String Functions: Extended FIND() Function
Last modified: 2017-05-20 11:13:50 UTC
The motto for OOo should be innovation and implementation of brilliant ideas instead of copying MS Office. Only in this way it can surpass its rivals. One issue where OOo continues to copy the bad quirks of MS Office is the string search/find function in Calc. As an example: lets type in an arbitrary cell in Calc: =find('string_to_find','cell where to find') IF 'the string_to_find' isn't found, this function returns an error code (#VALUE!), that breaks any further Calc function (i.e. it cannot be used as an argument in any other operation). WHY??? Why does it not return (int) 0. This seems the logical return value if the string is NOT found. As the the target string starts at position 1, a value of 0 does not interfere in any way with other results AND would permit to use the return value as a normal operand in any other operation. Another example: I would like to search if either 'str1' OR 'str2' is present: =(find("str1";A1;1) OR find("str2";A1;1)) => it DOES NOT work, because it returns an error if one of the strings is not found. This is definitely wrong behaviour!!! NOT finding a string is NOT an error, it should be just reporting a value beyond the normal string positions. A good example comes from the member function find from the C++ basic_string template class (basic_string::find). When the string is NOT found, it returns basic_string::npos, which is the SAME TYPE as any other search result (most often (unsigned int) (-1) ). It DOES NOT generate an error or raise an exception. This is the normal behaviour and is fully logical.
One for the requirements team
I would rather it returns -1 instead of 0 if the string is not found. In this specific case returning 0 may be okay, but too many of us are too used to 0-based positioning scheme (i.e. the value of 0 corresponds with the first column), relying on zero for the "not found" condition makes me feel uneasy. Just my opinion.
Created attachment 44238 [details] Patch Implementing an ScFindEx() function
The patch implements a more advanced FIND() function with the following characteristics: DESCRIPTION - string starts at position 1 - ends at =LEN(string) 1. IF string is NOT found, it returns 0 instead of an ERROR, therefore greatly simplifying complex operations with strings! 2. one can explicitly specify the START position within the string - IF START < 1, it automatically sets the START = 1 3. one can explicitly specify the END position within the string - IF END > LEN(string), it automatically sets END = LEN(string) 4. IF( START > END), it returns 0 By defining a START and END position, searching inside strings is greatly simplified. There is NO need to first compute a substring; rather it is possible to specify the substring directly inside the FindEx() function.
Hi Niklas, please have a look at this patch. Frank
.
*** Issue 77481 has been marked as a duplicate of this issue. ***
This is similar to issue 76156. We don't want too many new internal functions, and type "patch" should be limited to complete patches.
Issue 90675 deals with the same limitation of Calc's string functions. http://www.openoffice.org/issues/show_bug.cgi?id=90675
Cumbersome work-around example as found in bug 90675: =IF(ISERR(FIND("find_text";C2));0;FIND("find_text";C2))
Searching on the MS forums I saw some other requests that could well be handled by this extended function. FINDEX() Function Syntax: FINDEX(string, string, START, END) Initially, when I wrote the code for the FindEx() function, I did NOT interpret specifically a negative END position, BUT returned 0 IF START > END. I did played with the idea to interpret negative values as pointing to the _end_ of the original string, though the code would have become to complex and I abandoned the idea. The requests to search the end of the string (and even a reverse search) did pop up on the MS forums, so I think it is time to get into such an extended FINDEX() function, too. The idea: - IF (START < 0) => begin to search at string position: strlen() + START /* REMEMBER: START is negative */ - IF (END < 0) => end search at string position: strlen() + END /* REMEMBER: END is negative */ So, it would be possible to specify relative positions from the beginnineg or from the end of the original string. This function would not cover a reverse-search, however, one could similarly define an RSearchEx() function.
*** Issue 90675 has been marked as a duplicate of this issue. ***
this feature can break Excel compatibility. an XLS file with FIND+ISERR construct will be opened incorrectly (even worse, OOo will show completely wrong result for =IF(ISERR(FIND("a";"xxx"));0;1), because ISERR() will return FALSE instead of TRUE) however, there is also SEARCH() function, which act same as FIND. I think it is possible to make FIND work a-la Excel and SEARCH as proposed here (or vice versa). then XLS import filter can simply use Excel-friendly variant.
The behavior of SEARCH and FIND is defined in ODF 1.2. There is said, "Returns an Error if text not found." Any change in behavior would require to firstly change the standard.
*** Issue 105036 has been marked as a duplicate of this issue. ***
Reset assigne to the default "issues@openoffice.apache.org".