Created attachment 112459 [details] test ODS file VLOOKUP returns #N/A in the attached spreadsheet. The expected return value is "1". Steps to reproduce: 1. Open the attached ODS file; 2. Observe that cell B2 has formula "=VLOOKUP(A2,E1:F1,2,0)". A2 contains string "26*12-12-8 TL R1". Expected: Because Cell E1 contains the string "26*12-12-8 TL R1" to be lookup, so cell B2 should return "1" (cell F1) Current Behaviour: Cell B2 returns "#N/A". Win 7 X86 Version: 4.4.0.2 Build ID: a3603970151a6ae2596acd62b70112f4d376b990 Locale: zh_CN VLOOKUP is one of the most common used functions in spreadsheet, so this is a critical issue.
This issue seems the same as described in bug 49711. That bug was closed as NOTABUG per the following comment: https://bugs.freedesktop.org/show_bug.cgi?id=49711#c2 Which says disable "Allow regular expressions in formulas" solves the proble. However, in my opinion this is not the case, this is really a bug. "Allow regular expressions in formulas" means regular expressions in *FORMULAS* are allowed. But in the formula "=VLOOKUP(A2,E1:F1,2,0)" there are no regular expressions, only reference to cells. (The regular expressions are in cell references, not in *FORMULAS". If the devs insists that "Allow regular expressions in formulas" = "Allow regular expressions in formulas, including regular expressions in values returned by cell references", then the option "Allow regular expressions in formulas" should be disabled by default in Calc. Enabling this option will cause confuse for users (Who knows what special chars are regular expressions? and how many people know that we can use regular expressions in formulas?)
Well, let me count the reported regular expression issues which were closed as NOTABUG: Bug 83791 - SUMIF() not work with a string with parenthesis as criteria Bug 81395 - Function RICERCA(x;y;z) doesn't work Bug 72739 - MATCH function returns wrong result Bug 67633 - EDITING: SEARCH function doesn't find text in cells containing hyperlinks (Closed as FIXED, which is wrong status. hyperlinks contains "." which is a regular expression, this is the real cause of the bug) Bug 57368 - FORMULA SUMIF with brackets Bug 65043 - MATCH for text containing brackets returns #N/A Bug 43919 - "[" breaks vlookup Bug 42097 - Formula: Search criteria fail with parenthesis and bracket characters Bug 36740 - Function ZÄHLENWENN does not recognize a text with a '+' character ... (and there may be even more which didn't show in my bugzilla search results) (and there are even more issues the ordinary users encounter, don't know why their formula returns wrong value, but don't know where to ask questions / reporting bugs)
P.S. I am a 10+ years speadsheet user, and a 8+ years OpenOffice/LibreOffice user, but still didn't know the "Allow regular expressions in formulas" option. What about the other ordinary users? And what's the advantages of enabling this option by default? If a user knows what regular expressions are, and want to allow regular expressions in there formulas, then they can enable this option mannully. We should not enable it by default for all users.
Do I understand correctly that you propose "Set 'Enable regular expression' off by default" ?
(In reply to raal from comment #4) Yes correct. I set this as an enhancement request.
(In reply to Kevin Suo from comment #5) > (In reply to raal from comment #4) > Yes correct. I set this as an enhancement request. Changing summary.
(In reply to Kevin Suo from comment #3) > P.S. > I am a 10+ years speadsheet user, and a 8+ years OpenOffice/LibreOffice > user, but still didn't know the "Allow regular expressions in formulas" > option. What about the other ordinary users? > And what's the advantages of enabling this option by default? > > If a user knows what regular expressions are, and want to allow regular > expressions in there formulas, then they can enable this option mannully. We > should not enable it by default for all users. Sounds like a question for the UX Council of Elders (and young'uns). Status -> NEW Component -> ux-advise
Not really a UI/UX issue, back to the Calc guru's @Markus, Regina, Eike, * Is there anything forcing us to retain "enable regular expressions in formulas" as the default in Calc? Or would it, as Kevin suggests, be more reasonable not to--given the functions that regular expression parsing of Formulas apparently disrupts.
Might be possible to disable it per default, the actual option setting is stored with the document so existing documents using it should not be harmed. Needs to be verified. (In reply to Kevin Suo from comment #1) > However, in my opinion this is not the case, this is really a bug. > "Allow regular expressions in formulas" means regular expressions in > *FORMULAS* are allowed. > But in the formula "=VLOOKUP(A2,E1:F1,2,0)" there are no regular > expressions, only reference to cells. (The regular expressions are in cell > references, not in *FORMULAS". Not true. Affected is the lookup-value (string to be searched), it doesn't matter if the argument appears literally in the expression or is fetched through a reference or is the result of another expression. (In reply to Kevin Suo from comment #2) > Bug 67633 - EDITING: SEARCH function doesn't find text in cells containing > hyperlinks (Closed as FIXED, which is wrong status. hyperlinks contains "." > which is a regular expression, this is the real cause of the bug) Not true. Matched is the resulting cell string and that example works in more recent releases.
Note that upcoming 5.2 also will have wildcards implemented same as in Excel. See https://wiki.documentfoundation.org/ReleaseNotes/5.2#Support_wildcards_to_be_compatible_with_XLS.2FXLSX_and_with_ODF_1.2 Loading an Excel document of course uses wildcards instead of regular expressions. So, should we disable regular expressions and enable wildcards instead in new installations' user profiles for all new documents? My take is yes.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=4a2d67df5c3c44766adb0d395dffae61387c22a5 Resolves: tdf#88581 default for new installations is Wildcards on It will be available in 5.3.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=d77081946f59127aab4b017f455b0c5b489dcb84 RegularExpressions and Wildcards are mutually exclusive, tdf#88581 follow-up It will be available in 5.3.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=c48c5bad9d371da0e0c47c7ef5611a4afe4fd82e switch also internal default to Wildcards, tdf#88581 related It will be available in 5.3.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.