1. Calculation functions
  2. All Functions
  3. Miscellaneous Functions
  4. SUBSTITUTE

The SUBSTITUTE function replaces a single text character or a string of text characters with another.

The function takes three arguments that define the text to search, the text to find, and the replacement text. The search is case sensitive.

The search iterates through the text to search and replace exact matches of the text to find as they occur with the replacement text.

The function replaces exact matches of characters or strings only and does not accept the search patterns associated with a regular expression search, such as wild-cards.

This function is equivalent to the Excel function SUBSTITUTE.

Output

The function must be used in a line item with the Format: Text.

Syntax

SUBSTITUTE(“x”, “y”, “z“)        
Argument Description Format

Available Sources

x text to search Text Text variable, hard-coded value, line item, or property
y text to find Text Text variable, hard-coded value, line item, or property
z replacement text Text Text variable, hard-coded value, line item*, or property

* Anaplan does not accept a text space as line item content, a space must be hard-coded when used for either argument y or argument z.

Examples

Every incidence of text to find (aba) is replaced as the text to search (ababababa) is processed. The function replaces the first incidence of aba, then searches for the next. Each aba is replaced by c to return cbcba.

SUBSTITUTE(“ababababa”, “aba”, “c”)        

returns “cbcba

This example repeats the results, using line items as arguments.

SUBSTITUTE(Source, text to find , replacement text)        

No substitution occurs if the text to find argument (y) is empty.

SUBSTITUTE(“”, “”, “a”)        

returns “”

SUBSTITUTE(“some text”, “”, “XXX”)        

returns some text

Anaplan does not accept an empty text space as line item content. If a space is required as the replacement text argument (z), it must be hard-coded in the function.

SUBSTITUTE(“this_is_some_text”, “_”, “ ”)

returns this is some text

Similar functions