Using regular expressions in formulas

Regular expression can be used in the following formulas:

• Find() and Replace() formulas
• Lookup and matching formulas: Match(), vLookUp() and hLookUp()

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:

 abc finds cells containing (substrings) "abc" .bc finds cells containing three character substrings consisting of any character followed by "bc" \Aabc cell contents starting with "abc" abc\z cell contents ending with "abc" \Aabc.*123\z cell contents starting with "abc" and ending with "123" with any number of other characters in between abc\d\z substrings 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]+c substrings "abc", "aabc", "abbabc" etc. and not "c" [ab]*c substrings "abc", "aabc", "abbabc" etc. and "c" [^ab]+c substrings 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}c cell contents beginning with "ab", two or three digits and "c" abc|xyz cells containing substrings "abc" or "xyz"

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: abcdefghi Replace string: \s Replacement result: abc def ghi