Using regular expressions in functions

OpenOffice.org 3 Calc Guide

Chapter 7 Using Formulas and Functions

< Previous Section

Next Section >

• Creating formulas
• Understanding functions
• Finding and fixing errors
• Examples of functions
• Using regular expressions in functions

< Previous Page

Next Page >

A number of functions in Calc allow the use of regular expressions: SUMIF, COUNTIF, MATCH, SEARCH, LOOKUP, HLOOKUP, VLOOKUP, DCOUNT, DCOUNTA, DSUM, DPRODUCT, DMAX, DMIN, DAVERAGE, DSTDEV, DSTDEVP, DVAR, DVARP, DGET.

Whether or not regular expressions are used is selected on the Tools > Options > OpenOffice.org Calc > Calculate dialog.

Enabling regular expressions in formulas

For example =COUNTIF(A1:A6;"r.d") with Enable regular expressions in formulas selected will count cells in A1:A6 which contain red and ROD.

Additionally if Search criteria = and <> must apply to whole cells is not selected, then Fred, bride, and Ridge will also be counted. If that setting is selected, then it can be overcome by wrapping the expression thus: =COUNTIF(A1:A6;".*r.d.*").

Using the COUNTIF function.

Regular expression searches within functions are always case insensitive, irrespective of the setting of the Case sensitive checkbox on the dialog in Figure 22—so red and ROD will always be matched in the above example. This case-insensitivity also applies to the regular expression structures ([:lower:]) and ([:upper:]), which match characters irrespective of case.

Regular expressions will not work in simple comparisons. For example: A1="r.d" will always return FALSE if A1 contains red, even if regular expressions are enabled. It will only return TRUE if A1 contains r.d (r then a dot then d). If you wish to test using regular expressions, try the COUNTIF function: COUNTIF(A1; "r.d") will return 1 or 0, interpreted as TRUE or FALSE in formulas like =IF(COUNTIF(A1; "r.d");"hooray"; "boo").

Activating the Enable regular expressions in formulas option means all the above functions will require any regular expression special characters (such as parentheses) used in strings within formulas, to be preceded by a backslash, despite not being part of a regular expression. These backslashes will need to be removed if the setting is later deactivated.

Advanced functions

As is common with other spreadsheet programs, OOo Calc can be enhanced by user-defined functions or add-ins. Setting up user-defined functions can be done either by using the Basic IDE or by writing separate add-ins or extensions.

The basics of writing and running macros is covered in Chapter 12, Calc Macros. Macros can be linked to menus or toolbars for ease of operation or stored in template modules to make the functions available in other documents. Calc Add-ins are specialized office extensions which can extend the functionality of OpenOffice.org with new built-in Calc functions.

Writing Add-ins requires knowledge of the C++ language, the OOo SDK, and is for experienced programmers. More information is available on the OOo wiki page at http://wiki.services.openoffice.org/wiki/Calc/Add-In/Simple_Calc_Add-In. A number of extensions for Calc have been written and these can be found on the extensions site at http://extensions.services.openoffice.org/. Refer to Chapter 14, Setting up and Customizing Calc, of the Calc Guide for more details.