Filtering for a term / Using regular expressions

Discuss the spreadsheet application

Filtering for a term / Using regular expressions

Postby homoludens1000 » Tue Apr 15, 2008 2:27 pm

Dear all,

I have a question regarding filtering in OOo calc: I want to filter for cells that INCLUDE a particular term. However,it seems that the AutoFilter/Standard Filter functions only allow me find cells that CONSIST OF that term (or to be precise, the operators available are =, >, <, >=, <=, but not "contains"). Could someone help me out?

In simplified terms, I have a spreadsheet containing a vocabulary list with 3 columns: A (language1); B (language2); and C (comments). Some of the fields under column C include the term "EDITED" , and I'd like to filter for those ...

Thanks,

homo ludens

---------------------------
One more thing:
Is it possible to use regular expressions in filters? And if so, how?
Last edited by homoludens1000 on Tue Apr 15, 2008 3:58 pm, edited 2 times in total.
homoludens1000
 
Posts: 5
Joined: Tue Apr 15, 2008 2:19 pm

Re: Filtering for a term

Postby Villeroy » Tue Apr 15, 2008 3:00 pm

Menu:Tools>Options....Calc>Calculation:[X]Search criteria = and <> must refer to whole cells. This is a per-document setting.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28667
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Filtering for a term

Postby Dave » Tue Apr 15, 2008 3:23 pm

Would it not then be more appropriate, or at least simpler, to have a further column that will contain [or not] "EDITED", and sort/filter on that column?

David.
Dave
 
Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

Re: Filtering for a term

Postby homoludens1000 » Tue Apr 15, 2008 3:56 pm

Villeroy:
Thanks, that is what I was looking for. Too bad, though, that OOo calc don't include an operator "includes" (Excel does ... :roll: )

Dave:
Well, it was also a general question, I've come across this issue before. Unfortunately, one can't also predict in advance what kind of special columns one might need in a database later on -- and reformatting a large database isn't really an option.
homoludens1000
 
Posts: 5
Joined: Tue Apr 15, 2008 2:19 pm

Re: Filtering for a term

Postby Villeroy » Tue Apr 15, 2008 4:04 pm

homoludens1000 wrote:Villeroy:
Thanks, that is what I was looking for. Too bad, though, that OOo calc don't include an operator "includes" (Excel does ... :roll: )

Dave:
Well, it was also a general question, I've come across this issue before. Unfortunately, one can't also predict in advance what kind of special columns one might need in a database later on -- and reformatting a large database isn't really an option.

OpenOffice.org does even include a "pattern matching language" called "regular expressions". This is by far more than MSOffice has to offer. But regular expressions are not needed to solve your problem.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28667
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Filtering for a term / Using regular expressions

Postby homoludens1000 » Tue Apr 15, 2008 4:16 pm

Villeroy:
How can I use regular expressions for filtering? Where do I have to enter them?
(I'm familiar with regular expressions from other programs, so would LOVE to use them!)
homoludens1000
 
Posts: 5
Joined: Tue Apr 15, 2008 2:19 pm

Re: Filtering for a term / Using regular expressions

Postby Tribio » Tue Apr 15, 2008 4:20 pm

Suppose I want to filter my table on content of the column Name. I want to show all names which contain "hans" (without the quotes):
In the Standard Filter window, you then put the following:
Field name: Name
Condition: =
Value: .*hans.*
The dot followed by the asterisk symbolises that any amount of character can be as well in front as in the back of the string, as long as there is "hans" in it.

More information concerning regular expressions for this matter:
http://openoffice.blogs.com/openoffice/ ... ains-.html
Image
User avatar
Tribio
 
Posts: 5
Joined: Thu Mar 27, 2008 5:40 pm

Re: Filtering for a term / Using regular expressions

Postby homoludens1000 » Tue Apr 15, 2008 4:26 pm

Tribio: Excellent! Thanks for your help. :D
homoludens1000
 
Posts: 5
Joined: Tue Apr 15, 2008 2:19 pm

Re: Filtering for a term / Using regular expressions

Postby Villeroy » Tue Apr 15, 2008 4:36 pm

homoludens1000 wrote:Villeroy:
How can I use regular expressions for filtering? Where do I have to enter them?
(I'm familiar with regular expressions from other programs, so would LOVE to use them!)

I see. Regexes are implemented somewhat faulty, but in most cases they work as you might expect. After so many years of begging, the developers finally implemented braced (substring1) to be substituted by $1 in replace strings.
Calc-specific: When you open Excel files, the support of regular expressions in formulas is turned off (see Tools>Options...Calc>Calculation). It is turned on by default for all native OOo sheets including the older *.sxc files.
In Find&Replace and in filters you have to check "Regular Expression" in the respective dialog, usually hidden below [More Options...]. Then the search- or filter-expression is treated as regex.
There are some really strange bugs and quirks. FOr further reference:
http://wiki.services.openoffice.org/wik ... _in_Writer
http://wiki.services.openoffice.org/wik ... ns_in_Calc

Named classes such as [:alnum:] are "stand-alone" (not [[:alnum:]] nor [^[:alnum:]]) Regexes fail if the very last token is a named class. You need to work-around like [:alnum:]{1} in this case.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28667
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Filtering for a term / Using regular expressions

Postby huw » Tue Apr 15, 2008 4:47 pm

There is an enhancement request in for "Contains" as a filter choice - issue 35579

To use regular expressions you would filter for =
Code: Select all   Expand viewCollapse view
.*EDITED.*
with that option enabled in More...

See the Calc regex howto
huw
Volunteer
 
Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm

Re: Filtering for a term / Using regular expressions

Postby homoludens1000 » Wed Apr 16, 2008 12:35 am

Thanks, all. Your comments and/or links have all been extremely helpful!

homo ludens
homoludens1000
 
Posts: 5
Joined: Tue Apr 15, 2008 2:19 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 19 guests