Apache OpenOffice (AOO) Bugzilla – Issue 15339
VLOOKUP doesn't match regular-expressions
Last modified: 2017-05-20 11:42:06 UTC
Using: StarOffice 6.0 OpenOffice 1.1Beta Windows XP Home PC When the argument 'criterion' of a VLOOKUP function contains regular-expression-like characters (.?) and "Options-Spreadsheet-Calculate-Enable regular expressions in formulas" is turned on, function will always returns "N/A" (sort order "TRUE"). In the attached sample I commented various matches where i found the result astonishing. By the way online help titles "Allow regular expressions in formula" for the switch "Enable regular ...".
Created attachment 6681 [details] Sample OpenOffice spreadsheet
Hi, regular expressions are working from OOo1.1Beta2 on. The only thing using your example that can be seen as a bug is the search for a hard line break. All other work as defined. Calcs reg expressions compare what can be seen in the Edit line, therefore a hard line break will not be recognized for know. I sent this report as an RFE to our feature guys. Best regards Frank
Summary: VLOOKUP doesn't match regular-expressions. When the argument 'criterion' of a VLOOKUP function contains regular-expression-like characters (.?) and "Options-Spreadsheet-Calculate-Enable regular expressions in formulas" is turned on, function will always return "N/A" (sort order "TRUE"). This issue is considered for OO.o later.
Reviewing this in OOo 2.3: I disagree about the summary VLOOKUP *does* match regular-expressions - with the exception that E\nF does not find E line_break F. Note that Find & Replace will indeed find E\nF, but will *not* find E.F or even E.*F. As far as I can tell, the regex engine seems to consider OOo text in separate bits, delineated by paragraph marks and \n in Writer, and by cells and \n in Calc. It considers these bits one at a time separately, and thus does not normally see the delineators at all. Then because people want to be able to manipulate \n and paragraph marks, special cases have been added (eg $ on its own to match a paragraph mark). This E\nF in a function seems to be a special case that is not working as intended. Many people feel the overall OOo regex syntax should be re-thought - and if that happens then this issue might disappear. So I agree a target OOo later.
The patch in Issue 35913 fixes the regular expression problem matching line breaks (\n).
Grabbing.
Fixed with issue 35913 in cws calcmultiline.
Reassigning to QA for verification.
verified in internal build cws_calcmultiline
This issue is closed automatically and wasn't rechecked in a current version of OOo. This fixed issue should be integrated in OOo since more than half a year. If you think this issue isn't fixed in a current version (OOo 3.1), please reopen it and change the field 'Target Milestone' accordingly. If you want to download a current version of OOo => http://download.openoffice.org/index.html If you want to know more about the handling of fixed/verified issues => http://wiki.services.openoffice.org/wiki/Handle_fixed_verified_issues
Sorry this issue was wrongly closed. This issue will be reopened automatically. And will be set after that back to fixed/verified.
Set to state 'fixed'.
Set back to state 'verified/fixed'. Again. Sorry for the mass of mails.