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.