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 )


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

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


Let's look at some SUBSTITUTE_REGEX function examples.

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



1 white/123/dog


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

-> [white 123 dog]



1 Broadband - Television - Ottawa


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

-> [Ottawa]

Related links:

Text functions