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.
