Overview
Grammar | SUBSTITUTE(text, old_text, new_text, instance_num) | Substitutes new_text for old_text in a text string. |
Parameter 1 | text | Text string |
Parameter 2 | old_text | Old text string you want to replace |
Parameter 3 | new_text | New text string with which you want to replace old_text |
Parameter 4 | instance_num | Specifies which instance of old_text you want to replace. |
The SUBSTITUTE function is used to replace specific text in a text string.
The REPLACE function is used to replace any text in a specific location in a text string.
Notes
Parameters 1, 2, and 3 can be of any type, and Parameter 4 is of the numeric type (that can be defaulted).
- Direct connect data does not support this function.
Note:
Example
For example, if you want to replace Cold in the Product name column with Flu, enter the formula SUBSTITUTE(Product name, "Cold", "Flu"), as shown in the following figure.
More examples:
| Formula | Result | Notes |
|---|---|---|
| SUBSTITUTE("data base","base","model") | data model | |
| SUBSTITUTE("July 28, 2000","2","1",1) | July 18, 2000 | |
| SUBSTITUTE("July 28, 2000","2","1") | July 18, 1000 | |
| SUBSTITUTE("July 28, 2000","2","1",2) | July 28, 1000 |
