The regexreplace( function replaces text with new text. The text to be replaced is determined by a regular expression.


This function has three required parameters:

text – is the item of text that you want to search through and possibly replace part of.

pattern – is the regular expression that specifies what text should be replaced (see Regular Expressions).

template – is the replacement text, which may include all or portions of the text being replaced.

– The function may have additional pairs of pattern and template text, you can add as many pairs as you need.

…options – This parameter is optional. If it is included, it specifies the regular expression options. The parameter should be a number obtained by adding up the options you want from the table in the description section below.


This function is similar to the replace( function, but uses a regular expression to determine what text to replace (see Regular Expressions). Here is an example that replaces any numbers in the text with the word [REDACTED].

regexreplace("234 plus 189 is 423","[0-9]+","[REDACTED]")

If the replacement template contains $0, it will be replaced by the found (also called captured) text. This formula uses this technique to surround any numbers in the text with [ and ] brackets.

regexreplace("234 plus 189 is 423","[0-9]+","[$0]")
    ☞ [234] plus [189] is [423]

If your regular expression includes capture groups they can be included in the replacement template by using $1, $2, $3 etc.. This formula uses this technique to generate a very rudimentary form of obscured e-mail address.

regexreplace("Reply to or",
"([\w!#$%&'*+/=?`{|}~^.-]+)@([A-Z0-9.-]+)",||"$1 DOT $2"||)
    ☞ Reply to "support DOT" or "help DOT"

Notice that this regular expression has matched e-mail addresses in lower case, even though the regular expression specifies upper case letters. The regexreplace( function is case insensitive so it ignores differences between upper and lower case. If you want the regular expression to be case sensitive use the regexreplaceexact( function, or use the options parameter (see below).

The regexreplace( function must have at least three parameters, but it may also have 3, 5, 7, 9 or more parameters. If there are additional parameters, they must appear in pairs of pattern and template values. When there are multiple pairs, each pair replacement is performed from left to right. This example uses two replacement pairs – the first replaces numeric digits with the digit 0, the second that replaces all alphabetic characters with the letter A.

regexreplace("234 plus 189 is 423","[0-9]","0","[A-Z]","A")
    ☞ 000 AAAA 000 AA 000

The optional final parameter, options, allows you to customize the overall operation of the regular expression. To calculate the value for this parameter add up the options you want from the table below. For example, if you want the regular expression to be case insensitive and want it to ignore whitespace and comments, this parameter should be 3 (1+2). Note: If you specify an option value this overrides the normal case sensitive/insensitive operation of regexreplace/regexreplaceexact. If your option value specifies case insensitive the regex will be case insensitive whether you are using the regexreplace( or regexreplaceexact( functions.

0No options
1Case Insensitive - match letters in the pattern independent of case.
2Ignore whitespace and #-prefixed comments in the pattern.
4Treat the entire pattern as a literal string.
8Allow . to match any character, including line separators.
16Allow ^ and $ to match the start and end of lines.
32Treat only \n as a line separator (otherwise, all standard line separators are used).
64Use Unicode TR#29 to specify word boundaries (otherwise, traditional regular expression word boundaries are used).

If the options parameter is included then this function will always have an even number of parameters – 4, 6, 8, 10, etc.. The options parameter always comes after all of the pattern/template pairs.

See Also


10.0NewRegular expression support is new in this version