Apache OpenOffice (AOO) Bugzilla – Issue 83254
Regex parsing switch should default to false
Last modified: 2014-01-12 12:10:33 UTC
I just have an example. There's the sheet with 6 cells. Column B contains the formula A B C Aa (2) =MATCH(A1;C$1:C$1;0) Aa 2 Aa (2) =MATCH(A2;C$2:C$2;0) Aa (2) I expect to see in the first row string values don't match and in the second row values match. But the result is opposit: A B C Aa (2) 1 Aa 2 Aa (2) #N/A Aa (2) I believe the problem somehow is related to regular expression parsing. After I manually added into content.xml file the following : <table:calculation-settings table:use-regular-expressions="false"/> (I found it in the file exported by Editgrid) the result becomes to be as expected: A B C Aa (2) #N/A Aa 2 Aa (2) 1 Aa (2) I tested it in OO 1 and OO 2.
Hi, this is not a bug. You can switch the regular expression behaviour for formulas under tools-options-openoffice.org spreadsheet-Calculate-Enable regular expressions in formulas. It's a feature and is switched on by default. Frank
closed invalid
*** Issue 83308 has been marked as a duplicate of this issue. ***
Definitely I would change this default for Calc. By default processing of regular expressions should be OFF. Current default is very confusing and unusual for all people with other spreadsheet experience and also affects the spreadsheet compatibility. I would mention that most of text editors including OO Writer have by default the common string search and not regular expression. I believe this issue is critical.
Hi Frank, what do you think about this one ? IMHO we should keep the current default as most of our users are used to it. Also this makes working with MATCH and the LOOKUP functions more comfortable. The problem is just that old Excel users seems not to be able to learn that OOo Calc isn't Excel and something works different as they expect. There is always a learning curve. So the conclusion is keep it as it is. Frank
1. About old Excel users. I really would like OO to be the standard. But the truth is MS Office is the standard. I've been in IT for more than 20 years and I know what I am saying. More than half of all users don't know the word "spreadsheet". They pronounce only "excel spreadsheet". But leave Excel for a moment. There is another open source spreadsheet Gnumeric. Even more, the world is moving to Web interface. We have great Web applications: EditGrid spreadsheet, fast improving Google spreadsheet. All of them process MATCH function like Excel. 2. About consistency. If you open Find&Replace dialog in Calc you have "regular expressions" box unchecked by default, like in Writer and most known text editors. 3. Than you said "most of our users are used to it". I believe the majority of OO users don't use regular expressions. Not every programmer can use regular expressions and OO is design for office users not programmers. Thus it's much simpler for minority of advanced users to check "regular expressions" box after new installation than for majority of common users to understand why MATCH(A1,A2,1) works as expected but MATCH(A1,A2,0) works different. Thank you for your attention.
For document compatibility with other OpenDoc spreadsheets, the current behavior should be okay, since the regex setting is part of the spreadsheet document. With Excel spreadsheets, it shouldn't be too bad, since importing a .xls sets regex to off, which is the closest to Excel behavior, at least until issue 32344 gets fixed. So I'd interpret this issue as a request that newly created spreadsheets default to regexes off. I don't think this could break existing spreadsheets, since regex setting is part of the document. The argument is that users sophisticated enough to know about regexes probably also can be trusted to find the setting, but users unfamiliar with regexes seem likely to be surprised. Adjusting summary, changing to Enhancement.
Confirmed with AOO410m1(Build:9750) - Rev. 1555399 2014-01-06_04:13:46 - Rev. 1555657 Debian