How to do regular expressions in OpenOffice.org Calc functions

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.*")

 

---

---

Comments

[Bertilo]

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.

[bsdboy]

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.

[Solveig]

Hi bsdboy,

I think you'd express the negative as just <0

=IF(B1<0;"Negative";"Positive")

Solveig

[Charlene Lee]

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.

[Solveig]

Hi Charlene,

To do Contains, you do this type of thing:

.*bob.*

just put .* before and after what you want.

Solveig

[Jurgen Ludwig]

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;"????"

[Nisa]

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!!

[Jeoffrey]

Hi all!

How would I proceed if I wanted to count the words in a single cell using Calc?

Thanks in advance!

Jeoffrey

[a]

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

[George Drapeau]

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!