Learn Blog Pricing About Sign in

Learn

Blog

Pricing

About

Sign in

REGEXREPLACE function

REGEXREPLACE(text, regex, replacement, replaceAll?, caseInsensitive?, multipleLines?) REGEXREPLACE(text; regex; replacement; replaceAll?; caseInsensitive?; multipleLines?)

text

Text or { Text }

The text string, where the parts which match a regular expression are replaced.

regex

Text or { Text }

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

Logical or { Logical } (optional)

Whether all parts found by the regular expression should be replaced or only the first part. If omitted, FALSE is assumed.

caseInsensitive

Logical or { Logical } (optional)

Whether the regular expression should ignore case. If omitted, FALSE is assumed.

multipleLines

Logical or { Logical } (optional)

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

Text or { Text }

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":

REGEXREPLACE("regular expression", "(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:

REGEXREPLACE("regular expression", "(regular) (expression)", "second: $2, first: $1")REGEXREPLACE("regular expression"; "(regular) (expression)"; "second: $2, first: $1")
REGEXREPLACE("regular expression", "(regular) (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:

REGEXREPLACE("regular expression", "(regular) (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

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.)

REGEXREPLACE("Testing 1, 2, 3", "\d", "", TRUE)REGEXREPLACE("Testing 1, 2, 3"; "\d"; ""; TRUE)

Returns "Testing , , ". The fourth parameter, TRUE, instructs REGEXREPLACE to replace all matches with the replacement text.

REGEXREPLACE("abc def", "(abc) (def)", "2: $2, 1: $1")REGEXREPLACE("abc def"; "(abc) (def)"; "2: $2, 1: $1")

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.

REGEXREPLACE("abc def", "(abc) (def)", "2: " & INDEX(Groups, 2) & ", 1: " & INDEX(Groups, 1))REGEXREPLACE("abc def"; "(abc) (def)"; "2: " & INDEX(Groups; 2) & ", 1: " & INDEX(Groups; 1))

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.

REGEXREPLACE("abc def", "(abc) (def)", "2: " & REVERSE(INDEX(Groups, 2)) & ", 1: " & PROPER(INDEX(Groups, 1)))REGEXREPLACE("abc def"; "(abc) (def)"; "2: " & REVERSE(INDEX(Groups; 2)) & ", 1: " & PROPER(INDEX(Groups; 1)))

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.