regexreplaceexact( function

regexreplaceexact(

TEXT

PATTERN

TEMPLATE

...

...OPTIONS

)

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

Parameters

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.

Description

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]")
    ☞ [REDACTED] plus [REDACTED] is [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 support@acme.com or help@everyone.com",
"([\w!#$%&'*+/=?`{|}~^.-]+)@([A-Z0-9.-]+)",||"$1 DOT $2"||)
    ☞ Reply to support@acme.com or help@everyone.com

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

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.

0	No options
1	Case Insensitive - match letters in the pattern independent of case.
2	Ignore whitespace and #-prefixed comments in the pattern.
4	Treat the entire pattern as a literal string.
8	Allow . to match any character, including line separators.
16	Allow ^ and $ to match the start and end of lines.
32	Treat only \n as a line separator (otherwise, all standard line separators are used).
64	Use 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

regexarray( -- applies a regular expression to a text value, then builds an array containing all of the substrings that match the regular expression (see Regular Expressions).
regexarrayexact( -- applies a regular expression to a text value, then builds an array containing all of the substrings that match the regular expression (see Regular Expressions).
regexliteral( -- adds \ characters to text as necessary so that it can be used as a literal in a regular expression.
regexmatch -- checks to see if the text on the left matches the regular expression on the right (see Regular Expressions).
regexmatchexact -- checks to see if the text on the left matches the regular expression on the right (see Regular Expressions).
regexreplace( -- replaces text with new text. The text to be replaced is determined by a regular expression.
regexreplacefirst( -- replaces the first occurrence of a regular expression pattern with new text.
regexreplacefirstexact( -- replaces the first occurrence of a regular expression pattern with new text.
Regular Expressions -- Regular expressions can be used to look for a specified pattern in text and then specify what to do with each pattern match that is found

History

Version	Status	Notes
10.0	New	Regular expression support is new in this version