REPLACE (x,y,z,a)

Description:

This function "REPLACE" replaces part of a text string, based on the number of characters you specify, with a different text string.

Syntax: REPLACE (old_text, start_num, num_chars, new_text)

• Old_text : is text in which you want to replace some characters.
• Start_num : is the position of the character in old_text that you want to replace with new_text.
• Num_chars parameter : is the number of characters in old_text that you want this function to replace with new_text.
• New_text : is the text that will replace characters in old_text.

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.

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.

=REPLACE ("New York", 4, 5, "ark")	-equals "Newark"
=REPLACE ("1992", 1, 3, "200")	-equals "2002"
=REPLACE (" -YORK",-2, 0, "NEW")	-equals "NEW-YORK"

 

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 "Name" (which is the parameter that has the values for people's names), and the characters will be replaced starting from a specified position up to a specified position and replaced with the characters that are specified.

In this case, we have written a formula in the appropriate cube in PowerOLAP, pulling in the value from the above mentioned parameters and then replacing it with another value.



The formula above writes values into the "StringDataFunctions" cube to the "String Data Manipulations" dimension into the member named "Replace" by calculating the appropriate value for this member, based upon the parameters that are given as input to this function, namely the text value in the member "Name", the starting position number "4", replacing the value until the position number "5" and the value that is to overwrite the existing value are the characters "ji'. The result returned by the function is the text value which is replaced as directed with the new text value.