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
| Argument | Data type | Description |
| Text to search | Text | The text to search for instances of the Text to replace argument. |
| Text to replace | Text | The text value to substitute with Replacement text within Text to search in. Each instance of the text value is replaced. |
| Replacement text | Text | The text value to replace the Text to replace argument with. |
The SUBSTITUTE argument returns a text value.
Calculation engine functionality differences
In Polaris, the SUBSTITUTE function does not match the base characters of a composite characters. For example, the SUBSTITUTE function does not consider â to contain a or が to contain か.
In the Classic Engine, the SUBTITUTE function matches the base characters of composite characters. For example, the SUBSTITUTE function considers â to contain a or が to contain か.
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
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 performance | Financial performance in [Region Placeholder] changed by [Revenue Placeholder]. |
| Revenue against target | Revenue 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
| Tokyo | Munich | Tel Aviv | Abu Dhabi | |
| Revenue | $213,458 | $648,751 | $366,951 | $104,853 |
| Operating costs | $153,948 | $486,795 | $295,657 | $54,843 |
Operating profit
| $59,510 | 161,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
| Tokyo | Munich | Tel Aviv | Abu Dhabi | |
Financial performance template with 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
| 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.
