Regular expressions are a way for matching strings of text, e.g., particular characters, words, or phrases. They can be used in searching, editing and manipulating your data. You can use Java-style regular expressions in formulas by typing them into the formula or using them in a filter. Use them to process text for items such as email addresses, file names and extensions, or company names.
The following functions allow regular expressions to be used as arguments: |
Example:
If your workbook had a column listing all the users of your website, you could use the MATCHES() function and .*\d+.*
to find all user names containing numbers.
Or you could use the REGEXTRACT() function and [JMS].*
to tokenize all user names that start with "J", "M" or "S".
Here is a quick overview of how filters work with regular expressions. Refer to Filtering Data to learn more about filters.
Using regular expressions in simple filters:
Using regular expressions in advanced filters:
As with other types of regular expressions, Java regular expressions include pre-defined character classes, e.g.,
Datameer tries to match entire record without automatically adding ^ or $ to the regular expression. But if the record has multiple lines, then this must be accounted for with (?s) or line terminators. |
More information about regular expressions can be found on the web. Two sites we recommend are the Java Tutorials and 10 Regular Expressions You Should Know.
If you have created a regular expression and want to make sure that it is correct or if you need to change it into a java string then RegexPlanet's Regular Expression Test Page is just what you need.
See Importing with Regular Expressions to learn more.