SUBSTITUTE_REGEX function

The Klipfolio SUBSTITUTE_REGEX function replaces a set of characters with another set of characters in a text string. While the SUBSTITUTE function is similar, old text in the SUBSTITUTE_REGEX function is treated as a regular expression. This means that any special characters in old text can have an effect on what text is replaced.

 

* For more information on regular expression characters, see Summary of regular-expression constructs

The syntax for the SUBSTITUTE_REGEX function is:

SUBSTITUTE_REGEX ( text , old text, new text , occurrence )

Parameters

text The character(s) to be manipulated. text vector
old text The character(s) that will be replaced. text + regular expression characters * vector
new text The character(s) that are to replace the character(s) in old text. text vector
occurrence

Optional: Indicates the instance that will be replaced.

If the parameter is omitted every occurrence is replaced with the character(s) in new text.

number vector

SUBSTITUTE_REGEX examples

Let's look at some SUBSTITUTE_REGEX function examples.

The following examples are based on text from an Excel spreadsheet.

 

A

1 white/123/dog

 

SUBSTITUTE_REGEX( A:A , "/" , " " )  

-> [white 123 dog]

 

A

1 Broadband - Television - Ottawa

 

SUBSTITUTE_REGEX( A:A , ".*-" , "" , occurrence )  

-> [Ottawa]

Related links:


Text functions