Issue 66590

Summary: Calc: String Functions: Extended FIND() Function
Product: Calc Reporter: discoleo <discoleo>
Component: codeAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: inetpro, issues, rb.henschel
Version: OOo 2.0.2   
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: ENHANCEMENT Latest Confirmation in: ---
Developer Difficulty: ---
Description Flags
Patch Implementing an ScFindEx() function none

Description discoleo 2006-06-20 13:25:41 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 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

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.
Comment 1 frank 2006-07-10 15:14:51 UTC
One for the requirements team
Comment 2 kyoshida 2006-10-26 17:32:41 UTC
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.
Comment 3 discoleo 2007-04-05 21:47:42 UTC
Created attachment 44238 [details]
Patch Implementing an ScFindEx() function
Comment 4 discoleo 2007-04-05 21:58:09 UTC
The patch implements a more advanced FIND() function with the following

 - 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.
Comment 5 frank 2007-04-10 09:53:50 UTC
Hi Niklas,

please have a look at this patch.

Comment 6 frank 2007-04-10 09:54:22 UTC
Comment 7 gercokees 2007-05-21 09:19:48 UTC
*** Issue 77481 has been marked as a duplicate of this issue. ***
Comment 8 niklas.nebel 2007-06-08 15:25:58 UTC
This is similar to issue 76156. We don't want too many new internal functions,
and type "patch" should be limited to complete patches.
Comment 9 discoleo 2008-06-13 21:59:33 UTC
Issue 90675 deals with the same limitation of Calc's string functions.
Comment 10 inetpro 2008-08-07 17:31:55 UTC
Cumbersome work-around example as found in bug 90675:

Comment 11 discoleo 2008-08-12 22:17:16 UTC
Searching on the MS forums I saw some other requests that could well be handled
by this extended function.


 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.
Comment 12 redflagzhulihua 2008-08-22 10:44:33 UTC
*** Issue 90675 has been marked as a duplicate of this issue. ***
Comment 13 pmike 2009-02-10 20:19:55 UTC
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.
Comment 14 Regina Henschel 2009-09-13 16:39:51 UTC
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.
Comment 15 Regina Henschel 2009-09-13 16:42:29 UTC
*** Issue 105036 has been marked as a duplicate of this issue. ***
Comment 16 Marcus 2017-05-20 11:13:50 UTC
Reset assigne to the default "".