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