SUBSTITUTE function

Description

Substitutes one or all occurrences of a string in a text with a new string.

SUBSTITUTE(original_text, old_string, new_string[, occurrence_number]

original_text is the text you wish to substitute. It can be a simple string, a List property or a Metric.
old_string is the string in original_text that will be substituted. It must be a simple string, i.e. it can’t be a reference to a List property or the result of a function.
new_string is the string that will replace old_string . It must be a simple string, i.e. it can’t be a reference to a List property or the result of a function.
occurrence_number is an optional integer to indicate which specific occurrence of old_string in original_text to substitute. If not indicated or used with value 0, it will replace all occurrences of old_string. Can’t be negative.

Text

Case	Results	Result Type
`SUBSTITUTE(“Return on Investment”, “Investment”, “Equity”)`	Substitutes all occurrences of “Investment” by “Equity”. Since there is only one occurrence, it returns: `“Return on Equity”`	`Text`
`SUBSTITUTE(“Fimamcial Analyst”, “m”, “n”, 0)`	Substitutes all occurrences of “m” by “n”. There are multiple occurrences of “m”, it returns: `“Financial Analyst”`	`Text`
`SUBSTITUTE(‘Products’.’Name’, “Product number “, “#”, 1)`	Substitutes only the first occurrence of “Product number “ for all the values in the property ‘Name’ of the Dimension ‘Products’ by “#”.	`Text`

Excel equivalent: SUBSTITUTE(text, old_text, new_text, [instance_num])

Be the first to reply!