Apache OpenOffice (AOO) Bugzilla – Issue 63755
Misleading description (additional options of calc-filters)
Last modified: 2017-05-20 11:31:24 UTC
Online help on additional options of calc-filters (standard and advanced): <translated from german> Regular Expression: Allows placeholder in filter definition. If option Regular Expression is checked, you can use operators equal (=) and unequal(<>). Following funktions are availlable as well: DCOUNTV, DGET, MATCH, COUNTIF, SUMIF, LOOKUP, VLOOKUP and HLOOKUP. </translated from german> 1. The operators are availlable in any case, regex or not. 2. The (incomplete) listed D-functions need a defined criteria-range as well, but you can not define criteria with these functions. Well... you may use them when calculating advanced filter-criteria , but this is another point. MATCH, COUNTIF, SUMIF, LOOKUP, VLOOKUP and HLOOKUP (and some others like SEARCH) support regexes, but they have nothing to do with filters. The entire paragraph should be removed. Or did I miss some carefully hidden functionality?
I do not see these references in either of the current releases. 1.0.1 is _SO_ old that its Help will never be updated. I suggest that you upgrade to 1.1.5 or 2.0.2 as soon as possible and double check the Help.
This is in file text/scalc/01/12040201.xhp We are currently investigating why this text is there and what's the meaning. Please stay tuned.
Additionally the filter-documentation should mention the fact that option "= and <> match whole cells" has an effect on filtering (with regex it behaves like appendig a "$"). Unfortunately you have to use regex with "<>.+" in order to match empty cells. I think this special case should be mentioned also. (Excel matches empty cells with more obvious empty string =""). Another point related to regex-sensitive cell-functions (new issue?): Each one of those functions has a remark on regex-support pointing the reader to option "Enable Regexes in formulae". Option "= and <> match whole cells" should be mentioned as well.
> Unfortunately you have to use regex with "<>.+" in order to match empty cells. It's even more complicated: Unfortunately you have to use regex with "<>.+" and without option "= and <> match whole cells" in order to match empty cells.
ufi->nn: can you comment, please? is this a documentation issue or more than that?
First let me apologize for my slighly confusing late-night postings. The last one (Wed Apr 12 13:08:03 -0700 2006) is obsolete. Please remove it if you can. Analysing how regexes work with formulae, filters and search/replace I found some errors related to formulas while stumbling into a pitfall with option "Match Whole Cells". See http://www.openoffice.org/issues/show_bug.cgi?id=63849 Leaving aside the formula-related defects described in issue 63849 I think the documentation on filters should: 1. Remove that misleading paragraph unless I got something wrong in my original posting. 2. mention the fact that option "= and <> match whole cells" has an effect on filtering (with regex it behaves like appendig a "$"). 3. Give a hint how to match blank cells by regex "<>.+", because that is all but obvious. It could be "^$", "=^$" as well while Excel accepts an empty string (="") as criterion for matching blanks. 4. The documentation for each regex-aware sheet-function has a paragraph that points to option "Use Regex with Formula". That paragraph should include another pointer to option "= and <> match whole cells". The latter option does the same as appending a "$" to the regex. Points 1, 2 and 3 refer to filtering-chapters (3 *should* refer to formulas as well, but it does not work with formulas) Point 4 refers to regex-aware formulae-descriptions. I will post another enhancement-request, because I believe that option "= and <> match whole cells" should be irrelevant with regexes (simply append a "$" to your regex). I will post another enhancement-request, because I believe that Excel's behaviour (="" matches blanks) makes sence and this could work independantly of any option. Btw: On oooforum.org "How to match blank cells" is a repeating issue. Even advanced users, familiar with POSIX-regexes, have trouble with OOo-regexes. Lacking detailed information on the implementaion and dependancies from other options makes OOo unusable in regard of pattern-matching. So the ultimate, sad feature-request could be: "Forget about regexes. Do it like Excel!"
Addendum to point 3 above: I was told that excel matches blanks by formula ="", but that is true for empty strings only. Meanwhile I could start a win-PC and prove that Excel97 matches empty strings together with blanks by "<>*". Similary Calc matches empty strings together with blank cells by using "<>.*" as well as "<>.+" with regex filter-option. That's OK, but still not obvious enough and should be documented.
Posted an evaluation-spreadsheet at issue 64368 demonstrating all my regex-issues.
ufi->nn: please have a look if this is an issue of Calc code or documentation or a mix of both. Possibly, this can be closed?
Reset assigne to the default "issues@openoffice.apache.org".