SUBSTITUTE function

  • 19 January 2022
  • 0 replies
  • 80 views

Badge

Description

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

 

Syntax

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.

 

Return type

Text

 

Examples

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])


0 replies

Be the first to reply!

Reply