Apache OpenOffice (AOO) Bugzilla – Issue 90920
the regular expression '^$' doesn't work in search&replace, nor in standard filter
Last modified: 2013-08-07 15:12:27 UTC
I want to search for void cells and put 'X' in, so at first I'd use search&replace, but I've found 2 problems here: 1. replace( all) is greyed-out till something is written in 'serach' box. This is wrong,in IMHO, as a search for the NULL string (ie void cells here) isn't that exotic. 2. as a workaround, I tried RE, expecting '^$' to do the trick, but nope "search term not found". As a dumb test, I also tried '.*' which would match all and everything, but nope again - it matched cells with 1+ chars only; that is, '.*' behaves like '.+'. Same for .?. Likewise in 'standard filer' - true we have predefined '- void -' but when doing with RE ^$ (and others eg above) should work as expected nevertheless. If this is the _intended_ behaviour (and I can see the point), then it should be clearly stated (eg "void cells are skipped" | "match is only tried on non-void cells") in the help, since it's not the _expected_ (from RE usage) behaviour. thanks
The situations in OOo-dev300-m21_en as follows: 1.find(all)〠replace( all) are greyed-out till something is written in 'serach for' box ; 2.when 'regular expressions'checkbox is not selected,'.*','.?'and '^$'have the same results-pop-up"search key not found" dialogue; 3.when the 'regular expressions'checkbox is selected,'.*' and '.?'will search non-void cells,while '^$' pop-up"search key not found" dialogue. BTW,OOo_calc can't search only in selected cells area(MS_Excel works), that is ,calc can only search in the whole "worksheet" or "workbook", which may be the essential matter. (my OS is Windows Xp) Frankly.
Since Cheng Huai Lei (chl207) has confirmed this, and I also confirm this behaviour in DEV300_m21 Vietnamese (OSX Intel, 10.5.4), I am confirming this issue. The inability to search for a null string (especially via regex) is definitely a defect. The interface is also unintuitive in the case of a null string. Optimally, the Help and the interface should be amended to handle the null-string search situation.
Hi, the search term ^$ should find an empty paragraph, but you do not have any paragraph in a spreadsheet. So this seems to be a misuse of a reg. expression for Writer in Calc. Eike, what do you think about it ? Frank
The problem is more that an empty cell has _no_ content, not even an empty paragraph, and cells without content aren't searched. This could be enhanced to match empty cells for the '^$' expression as a special case. Including empty cells for an expression of '.*' or '.?' IMHO wouldn't make much sense as it would match every cell then. @oopla: to have the online help mention that only non-empty cells are searched, please file a separate issue for the documentation project. Thanks Eike
Not a RE misuse, IMHO: calc should handle it properly. OOo seems to use ERE; '^$' matches start-of-string,end-of-string, where start-of-string is the position at the very beginning of the string and end-of-string the very last one; from OOo help seems though ERE used are 'no-multi-line': {start,end}-of-string match the {start,end} position of a single line, ie the positions {after,before} end-of-line (aka 'paragraph' in OOo?), or the very {start,end} if there's no '\n'. So if Calc allows for multi-line (ie '\n' is legal) cell content: - '^$' would match any 0-length line. - likewise, '.*' would match every line while '.+' would match non-void lines only, since in no-multi-line ERE '.' doesn't match '\n'. - a single '\n' may be collapsed to a void cell, so it'd avoid the existance of such strange single-but-void paragraph. Else (no multi-line cell content, no '\n' allowed), '^$' would never match and '.*', '.+' would practically be equivalent, _if_ void cells are removed from (string) search space. And I do agree that it sounds reasonable to skip void cells: having no searchable content, they should not be in RE/filter (string) search space (besides, removing void cells from search space likely have huge impact on performance and resources usage). So it's a doc/help problem, but point above must first be clarified: is '\n' legal in cell content, ie can cell content be multi-line? thx -- paolo
This issue is related to (at least) issues 90658 and 89816. Calc's handling of null values is non-standard. Should we combine the info here?
No, this issue is not related to issue 90658 nor issue 89816. This issue here is about considering empty cells when searching for an empty string respectively the empty paragraph regular expression. Issue 90658 is about a formula's error result confusing the list of available values in AutoFilter. Issue 89816 is about the automatic selection of the data area if none was selected.
Maybe duplicate of this one : http://qa.openoffice.org/issues/show_bug.cgi?id=44688
Indeed, it's a dupe. @oopla: yes, cell content can be multi-line, use Ctrl+Enter to insert a linefeed, and will be found when using the \n regex. *** This issue has been marked as a duplicate of 44688 ***
Closing dup.