Using regular expressions in formulas

Regular expression can be used in the following formulas:

To use RE, each of these functions requires you to specify the SEARCH::RegEx (or 8192) flag/constant as its parameter as described in the Formula Composer window.

In the Find() function you can also use the SEARCH::RegExStr flag/constant. If it's specified, instead of the index the function returns the substringthat matches the pattern.
For example:

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

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

Some examples of regular expressions:

abcfinds cells containing (substrings) "abc"
.bcfinds cells containing three character substrings consisting of any character followed by "bc"
\Aabccell contents starting with "abc"
abc\zcell contents ending with "abc"
\Aabc.*123\zcell contents starting with "abc" and ending with "123" with any number of other characters in between
abc\d\zsubstrings ending with "abc" and one digit
^a\d+cell contents containing a line that starts with "a" and at least one digit
^a\d*cell contents containing a line that starts with "a" and zero or more digits
a\d*$cell contents containing a line that ends with "a" and zero or more digits
[ab]+csubstrings "abc", "aabc", "abbabc" etc. and not "c"
[ab]*csubstrings "abc", "aabc", "abbabc" etc. and "c"
[^ab]+csubstrings ending with "c" and not containing "a" or "b"
\w\d{2,3}substrings consisting of one letter followed by 2 or 3 digits
^ab\d{2,3}ccell contents beginning with "ab", two or three digits and "c"
abc|xyzcells containing substrings "abc" or "xyz"

For more information, see: PCRE regular expression syntax summary

The Replace() function additionally accept the replacing string which - if the SEARCH::RegEx flag is set - can contain:

  1. Absolute references (by number) to capturing subpatterns, eg. \1, \2...
    a capturing subpattern (or a "group") is a part of the pattern enclosed in () parenthesis.
  2. \l, \L, \u, \U literals to (binary) switch upper- and lower-case conversion.
  3. \r, \n - 'line feed' and 'new line' literals (by default, Ctrl+Enter inserts the \r\n sequence into the text field).
  4. \s - a single space character.

Example 1.

Find pattern:ab+
Database field content:abcdefaabb
Replace string:x
Replacement result:xcdefax

Example 2.

Find pattern:(.)a+\d{1,3}
Database field content:abc aa0102
Replace string:\1
Replacement result:abc 2

Example 3.

Find pattern:(ab)
Database field content: abcdef ghijk abb123
Replace string:\u\1\l\1xyz\s
Replacement result:ABabxyz cdef ghijk ABabxyz b123

Example 4.

Find pattern:\R
Database field content: abc
Replace string:\s
Replacement result:abc def ghi