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.
Let's look at some SUBSTITUTE_REGEX function examples.
The following examples are based on text from an Excel spreadsheet.
SUBSTITUTE_REGEX( A:A , "/" , " " )
-> [white 123 dog]
|1||Broadband - Television - Ottawa|
SUBSTITUTE_REGEX( A:A , ".*-" , "" , occurrence )