SUBSTITUTE (x,y,z)

Description:

This function substitutes new_text for old_text in a text string. It is advisable to use SUBSTITUTE when you want to replace specific text in a text string and to use REPLACE when you want to replace any text that occurs in a specific location in a text string.

Syntax: SUBSTITUTE (text, old_text, new_text); or
             SUBSTITUTE (text, old_text, new_text, start_num)

• Text : is the text or the reference to a cell containing text for which you want to substitute characters.
• Old_text : is the text you want to replace.
• New_text : is the text you want to replace old_text with.

Remarks

• All the arguments are compulsory.
• If you specify a number instead of a text value as this parameter, the function would return an error which is #VALUE
• You can specify a positive or a negative value for the start_num and the num_chars and the function will replace the text accordingly.
• Instance_num : specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

Example

The REPLACE function can be used in any database where you have stored any text values which you want to manipulate and replace with other text values. You can specify the text value you want replaced and also the position in that text value from where the text should be replaced. The function returns the new text value which is appropriately replaced with new text from the position specified as explained above.




=SUBSTITUTE ("USA", "A", "of America")	-equals "US of America"
=SUBSTITUTE ("1992", "199", "2002")	-equals "2002"

 

Note: A number supplied within double quotes is taken as a string parameter.

 
 

Let us take the example of a database in PowerOLAP where we have certain text values for name, last name, city, country etc., and we need to manipulate these text values and arrive at results with different permutations and combinations. We shall use this example in this case to replace some characters for the member called "Country", and the specified characters in this member will be substituted with the other characters specified later in the function. In this case, we have written a formula in the appropriate cube in PowerOLAP, pulling in the value from the above mentioned parameters , specifying the value to be substituted and then substituting it with another value.



The formula above writes values into the "StringDataFunctions" cube to the "String Data Manipulations" dimension into the member named "SUBSTITUTE" based upon the parameters that are given as input to this function, namely the text value in the member "Country", and therein we are substituting or replacing the value "A" and in its place we are substituting the value  "of America" The result returned by the function is the text value which is substituted as directed with the new text value.