Description
Use this function to replace occurrences of a specified substring within a text string with another substring. Supported in Chains.
SUBSTITUTE is useful for replacing specific text in a string, especially when you need to make multiple replacements.
Syntax
SUBSTITUTE(text, old_text, new_text, [instance_num])
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
text |
Yes | The text or the reference to a cell containing text in which you want to substitute characters. | A text string (including numbers), a cell reference containing the text string, or a formula which results in either of these. |
old_text |
Yes | The text you want to replace. | A text string (including numbers), a cell reference containing the text string, or a formula which results in either of these. |
new_text |
Yes | The text you want to replace old_text with. |
A text string (including numbers), a cell reference containing the text string, or a formula which results in either of these. |
instance_num |
No | Specifies which occurrence of old_text you want to replace. If omitted, every occurrence of old_text in text is replaced. |
A positive integer, a reference to a cell containing a positive integer, or a formula which results in either of these. |
Example
Sample data
A |
B |
||
|---|---|---|---|
| 1 | apple | banana | |
| 2 | grape | orange | |
| 3 | melon | lemon | |
| 4 | berry | lime |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Replace every occurrence of a letter within a word. | =SUBSTITUTE(A1, "p", "b") |
Replaces every "p" in "apple" with "b". Result: abble |
| Replace every occurrence of a letter within a word. | =SUBSTITUTE(B1, "a", "o") |
Replaces every "a" in "banana" with "o". Result: bonono |
| Replace only the first occurrence of a letter, leaving any later ones unchanged. | =SUBSTITUTE(A2, "a", "o", 1) |
Replaces just the first "a" in "grape" with "o" instead of every occurrence. Result: grope |
| Replace every occurrence of a letter within a word. | =SUBSTITUTE(B2, "e", "a") |
Replaces every "e" in "orange" with "a". Result: oranga |
| Replace every occurrence of a letter within a word. | =SUBSTITUTE(A3, "e", "i") |
Replaces the "e" in "melon" with "i". Result: milon |
| Replace every occurrence of a letter within a word. | =SUBSTITUTE(B3, "m", "p") |
Replaces the "m" in "lemon" with "p". Result: lepon |
Notes
- SUBSTITUTE is case-sensitive. If you need a case-insensitive replacement, consider using UPPER or LOWER to normalize the text case first.
- If
instance_numis specified, only the specified instance ofold_textis replaced. If omitted, all instances are replaced. - SUBSTITUTE does not support wildcards.
Tips
- Use SUBSTITUTE to clean or normalize data by replacing unwanted characters or substrings.
- Combine SUBSTITUTE with other text functions such as LEN, MID, or FIND to manipulate text strings more effectively.
- SUBSTITUTE can be nested to perform multiple replacements in a single formula.