This is a topic that you will best learn by trying out stuff for yourself – but here I summarize the basics…
There are a number of builtin functions that make use of regular expressions. Here, we use COUNTIF to illustrate some of the possibilities.
Regular expressions are a system for matching patterns in strings. They provide a very powerful set of tools for finding particular words or combinations of characters.
Based on my investigation, the following is a summary of the allowable constructs in OOo Calc.
- Most characters match themselves. There are exceptions – see below.
COUNTIF(A1:A100; “foo”) will return all the instances of the string “foo” in the specified range.
- “.” matches any single character.
COUNTIF(B2:B18; “..”) counts all cells with exactly two characters.
- “*” is a special character that matches zero or more occurences of the previous expression.
COUNTIF(B2:B18; “.*e”) counts all cells that end in “e”.
- “+” is a special character that matches one or more occurences of the previous single character.
COUNTIF(B2:B18; “.+m.+”) includes “Tmmy” and “name”, but not “my”.
- A string of characters enclosed in square brackets () matches any one character in that string.
COUNTIF(B2:B18; “[efg].*”) counts all cells starting with e, f, or g.