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

The SUBSTITUTE function finds all occurrences of a text value within another one, and replaces them with a given value.

For example, you can use the SUBSTITUTE function to change the content of a text string based on its dimensionality, such as region.

Syntax

SUBSTITUTE(Text to search in, Text to find, Replacement text)

Arguments

ArgumentData typeDescription
Text to searchTextThe text to search for instances of the Text to replace argument.
Text to replaceTextThe text value to substitute with Replacement text within Text to search in. Each instance of the text value is replaced.
Replacement textTextThe text value to replace the Text to replace argument with.

The SUBSTITUTE argument returns a text value.

Syntax example

SUBSTITUTE( Email content, "[Region placeholder]", Region name)

Additional Information

Search behavior

The SUBSTITUTE function only looks for exact text matches. It is not possible to use search patterns such as regular expressions or wildcards.

Substitution behavior

If the Text to search in value contains multiple instances of the Text to replace value, the SUBSTITUTE function replaces each of them. The replacement is in the order that the Text to replace values display, from left to right. However, it is not recursive. This means that if the Replacement text argument contains the text value from the Text to replace argument, the result is not substituted also.

If the Text to search in value does not contain the Text to replace value, the SUBSTITUTE function returns the Text to replace value unchanged. This means that if a blank value is used for the Text to replace argument, the SUBSTITUTE function has no effect.

Effect on different character types

The SUBSTITUTE function is case sensitive. It also works with emoji and other characters that are not part of the Unicode Basic Multilingual Plane.

The SUBSTITUTE function does not consider canonically equivalent characters to be the same character. For example, U+00E2 (Latin Small Letter A with Circumflex), which renders as â. This is not equivalent to the combination of U+0061 (Latin Small Letter A) and U+0302 (Combining Circumflex Accent), which also renders as â.

Excel equivalent

 SUBSTITUTE

Related Anaplan functions

Examples

General example of SUBSTITUTE

You can enclose text in double quotation marks to enter literal text values directly into the SUBSTITUTE function. In this example, each argument is enclosed in double quotation marks. The following formula:

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

searches the text ababababa for any instances of aba and replaces them with c. This means it returns a text value of cbcba.

SUBSTITUTE with text from a line item

This example uses data from three modules.

The first module, Email Templates, contains only text line items. Each of these line items contain the phrases [Region Placeholder] and [Revenue Placeholder], which are the text to be substituted using the SUBSTITUTE function.

Email Templates

Financial performanceFinancial performance in [Region Placeholder] changed by [Revenue Placeholder].
Revenue against targetRevenue in [Region Placeholder] was [Revenue Placeholder]. The target for this period was [Target Revenue Placeholder].

The second module, Profit and loss summary, contains the Cities list on columns. On rows, there are line items that contain a variety of financial results.

Profit and loss summary


TokyoMunichTel AvivAbu Dhabi
Revenue$213,458$648,751$366,951$104,853
Operating costs$153,948$486,795$295,657$54,843

Operating profit

Revenue - Operating costs

$59,510161,956$71,294$50,010

The third module, Revenue Email, has the Cities list on columns, and line items on rows. The line items contain two formulas that use data from the Email templates and Profit and loss modules to create text that changes based on the data within the modules.

Revenue Email


TokyoMunichTel AvivAbu Dhabi

Financial performance template with region

SUBSTITUTE(Email Templates.'Financial performance', "[Region Placeholder]", NAME(ITEM(Region)))

Financial performance in Tokyo changed by [Revenue Placeholder].Financial performance in Munich changed by [Revenue Placeholder].Financial performance in Tel Aviv changed by [Revenue Placeholder].Financial performance in Abu Dhabi changed by [Revenue Placeholder].

Financial performance template with region and revenue

SUBSTITUTE(Financial performance template with region, "[Revenue Placeholder]",  TEXT(Profit and loss summary.'Revenue') & " USD")

Financial performance in Tokyo changed by 213458 USD.Financial performance in Munich changed by 648751 USD.Financial performance in Tel Aviv changed by 366951 USD.Financial performance in Abu Dhabi changed by 104853 USD.

The second formula uses the result of the first formula for the Text to search argument. This is because each usage of the SUBSTITUTE function can search only for a single text value. In order to replace two different strings, you need to use the SUBSTITUTE function twice.

Replace underscores with spaces

You can use SUBSTITUTE to replace underscores, or other characters, in text with spaces. To do so, you must use a space enclosed in double quotation marks for the Replacement text argument. A line item cannot contain only a space, so this is the only method you can use.

SUBSTITUTE("Text_with_underscores", "_", " ")

The result of this formula would be Text with underscores, with the underscores replaced with spaces.