Bug 42097 - Formula: Search criteria fail with parenthesis and bracket characters
Summary: Formula: Search criteria fail with parenthesis and bracket characters
Status: CLOSED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.4.3 release
Hardware: All All
: medium major
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 42397 (view as bug list)
Depends on:
Blocks: mab3.4
  Show dependency treegraph
 
Reported: 2011-10-21 12:39 UTC by D S Hodgson
Modified: 2015-01-19 12:51 UTC (History)
4 users (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 D S Hodgson 2011-10-21 12:39:44 UTC
If you use the match function and the text strings contain brackets "(" or ")" then no match will be found.  This is with match type=0.

If the brackets are converted into, e.g. "_", in both search & table using convert then the text matches.

Other types of brackets not tested.  Other types of special characters not tested.
Comment 1 Christopher C. Alby 2011-10-25 13:11:23 UTC
I don't know what the proper procedure is here, but I would like to contribute the following additional information to this bug report:

Formula error:  Search strings containing parenthesis and bracket characters fail to return a match, i.e., the following characters do not work as search criteria: ( ) [ ]

e.g., if cell A1 contains:

  this (is) [a] test

and cell A2 contains:

  =COUNTIF(S7,"this (is) [a] test")

the result of A2's formula is returned as 0 when is should be 1.  Escaping the characters with single quotes gave the same result, e.g.:  =COUNTIF(S7,"this '('is')' '['a']' test").

This behavior is present for both literal and referenced strings.

I found this with the COUNTIF and VLOOKUP functions, but would speculate that it is probably effecting any or most database, reference, lookup, search and some mathematical functions.

The following characters do seem to work and might be used in workarounds: angle and curly brackets, i.e., < > { }

I tested this using Mac OS X 10.5.8 on a G5 Power PC (PPC) platform running LibreOffice LibreOffice 3.4.3, OOO340m1 (Build:302)
Comment 2 Christopher C. Alby 2011-10-25 13:17:20 UTC
Correction, browser crashed and I missed this on the retype, sorry:

My formulas above should have a cell argument of A1 not S7: 

....
and cell A2 contains:

  =COUNTIF(A1,"this (is) [a] test")

the result of A2's formula is returned as 0 when is should be 1.  Escaping the characters with single quotes gave the same result, e.g.:  =COUNTIF(A1,"this '('is')' '['a']' test").
....
Comment 3 vitriol 2011-10-30 05:57:59 UTC
*** Bug 42397 has been marked as a duplicate of this bug. ***
Comment 4 Regina Henschel 2011-10-30 06:22:55 UTC
Please read the help carefully:
"The search supports regular expressions. You can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must precede every character with a \ character. You can switch the automatic evaluation of regular expression on and off in Tools - Options - LibreOffice Calc - Calculate. "

Brackets have a special meaning in regular expressions. You are likely want to turn regular expressions off.
Comment 5 Regina Henschel 2011-10-30 06:23:31 UTC
No bug.
Comment 6 vitriol 2011-10-30 06:35:40 UTC
@Regina Henschel
OK, you are right. For all users: turn off the option Tools > Options... > LibreOffice Calc > Calculate > Enable regular expressions in formulas, if you don't want to use regex.
Comment 7 D S Hodgson 2011-10-30 09:47:00 UTC
How about changing the default to NOT have regex enabled ... for the people like me who didn't make the connection between regex and normal text matching, this would help.  I didn;t even know that regex was enabled by default  ...

The people who want to use regex will turn it on and can be expected to understand the implications of doing so (or a warning can be added in the appropriate place) ...

For me, this setup is totally opaque ...