I have a column titled “Names” with a lot of names. I’m trying to use the advanced filter (A1 through A30). I’m creating a separate column with the same title and below it (S* and S.*) to show names beginning with S (D4 and D5). What am I doing wrong?
How is selected in Menu>Tools>Options>LibreOffice Calc>Calculate - Formula wildcards?
Enable regular expressions in the extra options of the adv. filter dialog.
LO Help for Calc in the Advanced Filter topic states:
A matching expression can be:
- Other Text value. If the property Search criteria = and <> must apply to whole cells is true, the comparison is against the entire cell contents, if false, comparison is against any subpart of the field that matches the criteria. The expression can contain text, numbers, regular expressions or wildcards (if enabled in calculation options).
Does the above (if enabled in calculation options) even apply?
It’s complicated. All these calculation options are stored with the document.
“= or <> applies to whole cell” applies to the advanced filter, but not option “case sensitive”.
The regex setting of the filter (under “Options” in the adv. filter dialog) can be used to override the “whole cell” option.
Where does option “case sensitive” apply anyway?
AdvancedFiltering.ods (34.3 KB)
Thank you for your insight.
Yes, it is. Everything should be made as simple as possible, but not simpler.
Again, what is meant by “regular expressions or wildcards (if enabled in calculation options).”? The link takes you to the entry “Enabling wildcards in formulas” in the Help topic “Calculate”.
In case of Advanced Filter, regex works if the Regular expressions checkbox is checked in the filter options. It seems the status of Formulas Wildcards in Tools ⟶ Options ⟶ LibreOffice Calc ⟶ Calculate is unrelated. Note in this context tdf#121854 – “Make available wildcards with Standard and Advanced filters like in find & replace”.
On the other hand, one sees an indentical entry in the description of most of the functions mentioned in the said Help topic (“This relates to database functions and the LOOKUP, VLOOKUP, HLOOKUP, MATCH, AVERAGEIF, AVERAGEIFS, COUNTIF, COUNTIFS, SUMIF, SUMIFS, and SEARCH functions.”). In this case using wildcards or regular expressions in the search criteria does actually depend on settings in Tools ⟶ Options ⟶ LibreOffice Calc ⟶ Calculate (cf. a trivial example in the attached file). exampleDBfunc.ods (17.8 KB)
Hence my repeated question…
The wildcard options within the calculation settings do not apply to filters. Since LO includes a powerful REGEX function, I turn all pattern matching OFF.
The filters have their own regex setting.
The only calculation option that interferes with filters is the “match whole cell” thing.
In my sample it is turned on, and the regexes with .* allow partial matching anyway.
If you remove the regex option from the filter, remove the .* and turn off “match whole cell” in the options, partial matching works just as well. This applies to your sample too. Remove the dots and asterisks, turn off whole cell matching and the DCOUNT counts all cells having an “a” case-insensitively.
How I increased my level of simplicity:
My default template for Calc matches whole cells with no pattern matching. This default is what I need in most cases and it avoids mistakes related to literal braces, points asterisks etc… Whenever I need partial matching, I have 2 options:
- turn off “match whole cells”.
- use FIND (case-sensitive) or SEARCH (case-insensitive). I never turn on pattern matching.
SEARCH("a?b(c*d)e\f";A1)finds the literal stringa?b(c*d)e\fcase-insensitively anywhere within A1.
For anything beyond literal matching, there is REGEX and/or the PyString-AddIn.
1 Like