REGEXREPLACE function
Text
The text string, where the parts which match a regular expression are replaced.
Regex
The JavaScript regular expression used to find the text which is replaced. This regular expression may use capturing groups, which can be referenced from the replacement text string.
Replacement
Either a text string replacing the parts found by the regular
expression or a formula fragment returning said text. If it is a plain
text string, it may reference a capturing group from the regular
expression (a backreference) by writing a dollar sign
($) followed by the position of the capturing group,
starting with one ($1, $2, etc). Write
${1} instead of $1 if $1 is
followed directly by a number. $& inserts the entire text
string that matched the regular expression, $` inserts the
text to the left of the matched text and $' inserts the
text to the right of the matched text.
If this parameter is a formula fragment, it has access to the following
values: Match, the entire text string that matched the
regular expression (equivalent to $&),
Groups, an array whose elements are the text strings
captured by the capturing groups of the regular expression, with one
element per capturing group (equivalent to $1,
$2, etc), Offset (the position of the text
string that matched the regular expression, starting at position 1) and
OriginalText, the first parameter to REGEXREPLACE.
ReplaceAll
Whether all parts found by the regular expression should be replaced or only the first part. If omitted, FALSE is assumed.
CaseInsensitive
Whether the regular expression should ignore case. If omitted, FALSE is assumed.
MultipleLines
Whether the regular expression anchors ^ and
$ should be relative to a line of text or to the text
string in its entirety. If omitted, FALSE is assumed.
Returns
The resulting text string after all parts found by the regular expression have been replaced.
Replaces the parts of a text string that match a JavaScript regular
expression and returns the result. REGEXREPLACE("Testing
1, 2, 3", "\d", "")REGEXREPLACE("Testing
1, 2, 3"; "\d"; "") replaces the first part that matches the regular
expression "\d" with the empty text string — effectively removing it — and
returns the result: "Testing , 2, 3". (The regular expression "\d" matches a
single number.)
The fourth parameter to REGEXREPLACE may be set to TRUE to replace all parts of the first parameter that match the regular expression. REGEXREPLACE("Testing 1, 2, 3", "\d", "", TRUE)REGEXREPLACE("Testing 1, 2, 3"; "\d"; ""; TRUE) returns "Testing , , ".
Regular expressions are a powerful tool to search through and manipulate text, though they take time to master. For an introduction, refer to the blog post we wrote when Calcapp gained support for regular expressions.
Capturing groups
REGEXREPLACE supports capturing groups, which make it possible to identify specific parts of a text string. They are added to a regular expression by enclosing these parts in parentheses. "(regular) (expression)" is a regular expression matching the text string "regular expression" with two capturing groups that capture the two words "regular" and "expression".
The replacement text may reference the text captured by these capturing
groups (through backreferences). To reference the text captured by a
group, write a dollar sign ($) followed by the position of the
capturing group, starting with 1 ($1, $2, etc).
This formula returns "second: expression, first: regular":
(expression)", "second: $2, first: $1")REGEXREPLACE("regular expression"; "(regular)
(expression)"; "second: $2, first: $1")
Write ${1} instead of $1 if $1 is
followed directly by a number.
The replacement text can incorporate special values other than the text
captured by capturing groups. $& references the entire text
string that matched the regular expression, $` references the
text to the left of the matched text and $' references the text
to the right of the matched text.
Using a formula fragment instead of replacement text
The third parameter can either be replacement text, which can use special
values like $1, ${2}, $&,
$` and $', or it can be a formula fragment. The
text returned by a formula fragment cannot contain special values like
$1 and $&. Instead, the formula fragment has access
to equivalent special values that help it do its work.
Here are two equivalent REGEXREPLACE formulas. The first one uses plain replacement text and the second uses a formula fragment:
(expression)", "second: $2, first: $1")REGEXREPLACE("regular expression"; "(regular)
(expression)"; "second: $2, first: $1")
(expression)", "second: " & INDEX(Groups, 2) & ", first: " & INDEX(Groups, 1))REGEXREPLACE("regular expression"; "(regular)
(expression)"; "second: " & INDEX(Groups; 2) & ", first: " & INDEX(Groups; 1))
Both invocations of REGEXREPLACE return "second: expression, first: regular". (The & operator joins text strings together.)
The third parameter has access to special values, among them
Match, the entire text string that matched the regular
expression (equivalent to $& when using a plain replacement
string) and Groups, an array with the text captured by the
capturing groups of the regular expression (equivalent to $1,
$2, etc, when using a plain replacement string).
Use a formula fragment instead of a plain replacement string when you need access to the full power of the formula language to determine the replacement text. Consider this formula:
(expression)", "second: " & REVERSE(INDEX(Groups, 2)) & ", first: " & PROPER(INDEX(Groups, 1)))REGEXREPLACE("regular expression"; "(regular)
(expression)"; "second: " & REVERSE(INDEX(Groups; 2)) & ", first: " & PROPER(INDEX(Groups; 1)))
This formula returns "second: noisserpxe, first: Regular". INDEX(Groups, 2)INDEX(Groups; 2) returns the text of the second captured group, "expression", and reverses it using REVERSE. INDEX(Groups, 1)INDEX(Groups; 1) returns the text of the first captured group, "regular", and makes it use "proper" capitalization using the PROPER function.
Examples
Replaces the first part that matches the regular expression "\d" with the empty text string — effectively removing it — and returns the result: "Testing , 2, 3". (The regular expression "\d" matches a single number.)
Returns "Testing , , ". The fourth parameter, TRUE, instructs REGEXREPLACE to replace all matches with the replacement text.
Returns "2: def, 1: abc". $1 and $2 are
backreferences, enabling the replacement text to refer back to the text
captured by the capturing groups of the regular expression.
Returns "2: def, 1: abc". The third parameter is a formula fragment,
which has access to the full power of the formula language. It can use
special values which allow it to do its work, including
Groups, an array of the text captured by the capturing
groups of the regular expression, with one element per capturing group.
INDEX
returns an array element at the position indicated by its second
parameter. As such, INDEX(Groups,
2)INDEX(Groups; 2) is
equivalent to $2 when using plain replacement text.
Returns "2: fed, 1: Abc". The third parameter is a formula fragment,
which has full access to the full power of the formula language. It has
access to special values which allow it to do its work, including
Groups, an array of the text captured by the capturing
groups of the regular expression, with one element per capturing group.
INDEX
returns an array element at the position indicated by its second
parameter. As such, INDEX(Groups,
2)INDEX(Groups; 2) is
equivalent to $2 when using plain replacement text.
REVERSE
returns a version of its sole parameter with its characters reversed and
PROPER returns
a version of its sole parameter with changed capitalization.