REPLACE

Use the REPLACE function to replace characters or patterns in a string.

Syntax: REPLACE(sourceStr,pattern,targetStr)

For example, you can apply a replace function to media buy key values, replacing hyphens with spaces:

Media buy key values:

• Summer-sale-123
• New-deal-news
• ABC publish

Formula: REPLACE(csv['media buy key'],'-',' ')

Output:

• Summer sale 123
• New deal news
• ABC publish

All hyphens are removed and replaced with spaces. The third value with no hyphens remains the same.

The REPLACE function also supports the use of regular expressions. For example, the regular expression \s refers to all white-space characters. An additional backslash is added when using a regular expression with letters and numbers, such as \s, \0.

For example, you can apply the REPLACE function to replace spaces with hyphens.

Formula: REPLACE(csv['media buy key'],'\\s','-')

Output: 'media-buy-key'.

To use a special character, for example, an asterisk—considered a regular expression, as a regular character, add '\\' before the character.

For example:

Formula: REPLACE(csv['me*a buy key'],'\\*','di')

Output: 'media buy key'.

If the changes you want to apply to a string don’t require a regular expression, it’s recommended to use the SUBSTITUTE function.