Usage 1: Replacing Specified Content of a Text
Overview
Grammar | REPLACE(text,textorreplace,replacetext) | Replaces the specified character string in the original text with a different character string. |
Parameter 1 | text | Text or referenced cell containing the characters to be replaced |
Parameter 2 | textorreplace | Specified character string |
Parameter 3 | replacetext | Text in which the original character string needs to be replaced |
Notes
The function supports three parameters of any type.
Example
For example, if you want to replace Cold in the Product name column with Flu, as shown in the following figure.
Add a calculation indicator and enter the formula REPLACE(Product name},"Cold","Flu"), as shown in the following figure.
More examples:
| Formula | Result | Notes |
|---|---|---|
| REPLACE("abcd","a","re") | rebcd | - |
| REPLACE("a**d","**d","rose") | arose | - |
Usage 2: Replacing the String Starting from a Specified Position of a Text
Overview
Grammar | REPLACE(old_text,start_num,num_chars,new_text) | Replaces a specified number of characters in a text, with a different character string. |
Parameter 1 | Old_text | Text or referenced cell containing the characters to be replaced |
Parameter 2 | Start_num | Starting position of the text where old_text is to be replaced with new_text |
Parameter 3 | Num_chars | Number of characters in old_text to be replaced with new_text |
Parameter 4 | New_text | Text in which the original character string needs to be replaced |
Notes
The function supports four parameters, among which the first and fourth are of any type, and the second and third are numeric.
Example
Take the data desensitization of telephone numbers as an example.
| Formula | Result | Notes |
|---|---|---|
| REPLACE("0123456789",5,4,"*") | 0123*89 | / |
| REPLACE("1980",3,2,"99") | 1999 | / |
