The regexreplaceexact( 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].

regexreplaceexact("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.

regexreplaceexact("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.

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

Wait – this formula didn’t do anything at all! That’s because the regular expression specifies upper case letters only, and the e-mail addresses supplied are lower case. So no match. The regexreplaceexact( function is case sensitive, so you have to include both upper and lower case in your regular expression, as shown here (note the A-Za-z in this example).

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

Another way to achieve this would be to use the regexreplace( function, or to use the options parameter (see below).

The regexreplaceexact( 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 three replacement pairs – the first replaces numeric digits with the digit 0, the second that replaces all upper case alphabetic characters with the letter A, and a third that replaces lower case letters with a.

regexreplace("234 Plus 189 IS 423","[0-9]","0","[A-Z]","A","[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 both regexarray( and regexarrayexact(. If your option value specifies case insensitive the regex will be case insensitive whether you are using the regexreplaceexact( or regexreplace( 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