I talked in this post about how to use regular expressions in filters. The key point is that where you would use * in Excel, you use .* in Calc.
Filters aren't the only place where regular expressions come up. Let's say you want to count the number of people whose last name ends in "son" in a big list of names, range C5:C300. You can use COUNTIF. Here's an example. First you have the range, then the text you want to find in the range. The result is the number of time that text was found in the range.
I hit Return and I get the correct result, 2. (Only 2 because I'm lazy and didn't create 300+ sample names for this blog. ;> )
Now, the reason that the correct result is shown is that I have this option marked under Tools > Options > OpenOffice.org Calc > Calculate. It's pretty straightforward: "Enable Regular Expressions in Formulas."
If I unmark that option and click OK, then I get 0 as my result. So if you do regular expressions in formulas, keep it marked.
Note: I've also been told that you should deselect Search Criteria = and <> Must Apply to the Whole Cell but I haven't noticed an effect one way or the other.
Here's some additional guidance for using regular expressions.
Info on the OpenOffice.org wiki
Syntax examples for COUNTIF(), counting cells that meet the specified criteria in the range B2:B35. Use the same symbols for other functions that allow regular expressions.
Note: There aren't any handy dropdown lists in Filter windows so that you can select "Contains" or "Starts with". You need to type out the syntax. However, the following table should help.
What you want |
What to type in the cell where the count should appear |
Count the number of cells that contain only the word Linux |
=COUNTIF(B2:B35;"Linux") |
Count the number of cells that begin with Linux |
=COUNTIF(B2:B35;"Linux.*") |
Count the number of cells that are not equal to Linux |
=COUNTIF(B2:B35;"<>Linux") |
Count the number of cells that do not begin with Linux |
=COUNTIF(B2:B35;"<>^Linux.*") |
Count the number of cells that do not end with Linux |
=COUNTIF(B2:B35;"<>.*Linux$") |
Contains Linux |
=COUNTIF(B2:B35;".*Linux.*") |
Does not contain Linux |
=COUNTIF(B2:B35;"<>.*Linux.*") |
Thanks! I'd somehow managed to miss the COUNTIF function. I just replaced a few ridiculously complex formulas with simple COUNTIF functions using basic regular expressions. Great! This blog is really useful.
Posted by: Bertilo | February 08, 2008 at 05:18 AM
Hello,
Is is possible to use regexp in the IF-function as well?
I've tried this function without any luck; IF(B1=-.*;"Negative";"Positive")
Any tips would surely be appreciated.
Posted by: bsdboy | February 15, 2008 at 05:09 PM
Hi bsdboy,
I think you'd express the negative as just <0
=IF(B1<0;"Negative";"Positive")
Solveig
Posted by: Solveig | February 15, 2008 at 05:21 PM
is the filter function able to filter and show the rows which contain certain word? In excel, this could be done by using "contain", however when i try to do the same thing in calc, it seems doesnt work. pls advise.
Posted by: Charlene Lee | May 17, 2008 at 04:50 AM
Hi Charlene,
To do Contains, you do this type of thing:
.*bob.*
just put .* before and after what you want.
Solveig
Posted by: Solveig | May 17, 2008 at 10:18 AM
Hi - need the formula for the following conditions
I have the following numbers
9 - 15 - 23 - 28 - 39 - 48
in cells A1 to A6
What will the "countif" formula be for wanting to know how many cells hold numbers >20 but <30 ? ie cell A3 & A4
=countif(A1:A6;"????"
Posted by: Jurgen Ludwig | January 21, 2009 at 12:23 PM
THANK YOU!! I am used to Excel (and the * wildcard), and have been going NUTS on this one, so simple, it's no longer *, it is .*!!! You saved me!!
Posted by: Nisa | January 30, 2009 at 12:32 PM
Hi all!
How would I proceed if I wanted to count the words in a single cell using Calc?
Thanks in advance!
Jeoffrey
Posted by: Jeoffrey | February 18, 2009 at 09:11 AM
I am trying to get this formula to work in a table using openoffice writer
=EOMONTH(;1)
The function is to obtain the end of the month date and add 1 month
Any ideas
Posted by: a | March 03, 2009 at 03:35 AM
Thank you very much for posting this, and other blog entries. I was having trouble using the filter feature of Data Pilot tables in Calc (I'm trying to take a set of results and filter out those having the string "SSE" in them), and this blog entry told me exactly what I needed to know (use ".*SSE.*" instead of "*SSE*", as Excel would have me do).
Excellent! Thanks!
Posted by: George Drapeau | July 07, 2009 at 05:33 PM
Hello, Your blog is awesome :-). I was wondering how to use COUNTIF when the cells are not in a range or a separate like maybe =COUNTIF(B13;B15; ">=0". I keep getting error 504
Posted by: Denis | January 07, 2010 at 04:27 AM