OpenOffice Calc Tips

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.

This entry was posted on Tuesday, November 22nd, 2005 at 9:17 pm and is filed under Using OpenOffice Calc. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.