Data Transformation Engine Functions and Expressions Reference Guide

Chapter 3 - Function Reference SUBSTITUTE
Functions and Expressions Reference Guide
194
SUBSTITUTE
The SUBSTITUTE function replaces specified text with other specified text.
Note The SUBSTITUTE function has been extended to include multiple pairs.
Syntax
SUBSTITUTE ( single-text-expression { , single-text-
expression , single-text-expression } )
Meaning
SUBSTITUTE (item_to_convert, one-or-more-text-substitution-
pairs)
Where each one-or-more-text substitution-pair is
text_to_change , substitute_text
Returns
This function returns a single-text-item.
This function returns the text string that results from replacing all instances of
the first text_to_change with substitute_text in item_to_convert, then replaces
all instances of the second text_to_change with the substitute_text in the
result of the first substitution, and so on.
Examples
SUBSTITUTE ( "123*456*7" , "*" , "/" )
finds
123*456*7 and returns 123/456/7
SUBSTITUTE ( "120-45-6789" , "-" , "" )
finds 120-45-6789 and returns 120456789
The following example illustrates multiple searches for the SUBSTITUTE
function:
=SUBSTITUTE ("ABBA" , "B", "A", "A", "B")
The first search and replace finds all the "
B"s and returns "A"s (AAAA). The
second search and replace finds all the "
A"s and returns "B"s (BBBB). The end
result is a return of
BBBB.