« Google Analytics | Main | Sheet/Cell Protection 101 »
November 22, 2005
Regular Expressions 101
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.
Posted by Dave at November 22, 2005 01:36 AM
Comments
I would have appreciate more content on this tip. The form of the cells looks intuitive but I can see some newbies reading it more than one time to understand what exactly are you doing.
Posted by: JZA at December 1, 2005 10:19 AM