Using Regular Expressions in Formulas

1. Overview

Regular expressions can be used in:

To enable regular expressions, specify the SEARCH::RegEx flag (value 8192) as described in the Formula Composer window.

In Find(), you may also use SEARCH::RegExStr. If used, the function returns the matching substring instead of the index.

Examples:

=find("\w\d{2,3}", "abc4567ghr", 1, SEARCH::RegEx) → 3

=find("\w\d{2,3}", "abc4567ghr", 1, SEARCH::RegExStr) → "c456"

You can specify which capturing group should be returned:

Useful for extracting file components:

Extension:
=find("(\.[^.]+)$", "c:\file.txt", 1, 32+SEARCH::RegExStr)

Folder:
=find("^(.*[\\\/])?(\.*.*?)(\.[^.]+?|)$", "c:\folder1\file.txt", 1, 32+SEARCH::RegExStr)

File name without extension:
=find("^(.*[\\\/])?(\.*.*?)(\.[^.]+?|)$", "c:\file.txt", 1, 64+SEARCH::RegExStr)

abc	Cells containing “abc”
.bc	Any character + “bc”
\Aabc	Starts with “abc”
abc\z	Ends with “abc”
\Aabc.*123\z	Starts with “abc”, ends with “123”
abc\d\z	Ends with “abc” + digit
^a\d+	Line starting with “a” + digits
^a\d*	Line starting with “a” + optional digits
a\d*$	Line ending with “a” + optional digits
[ab]+c	“abc”, “aabc”, “abbabc”… but not “c” alone
[ab]*c	Same as above, including “c”
[^ab]+c	Ends with “c”, contains no “a” or “b”
\w\d{2,3}	Letter + 2–3 digits
^ab\d{2,3}c	Starts with “ab”, 2–3 digits, “c”
abc\|xyz	Contains “abc” or “xyz”

If SEARCH::RegEx is set, the replace string may contain: