The SUBSTITUTE function replaces a certain text in a string with another one.
This function can be used when you need to change certain words or characters in the text without editing it manually.
Syntax
SUBSTITUTE(string, old_text, new_text, [instance_num])
Argument |
Description |
Permitted values |
|---|---|---|
string |
String in which the text needs to be replaced |
Text string or reference to a cell containing text |
old_text |
Text to be replaced |
Text string or reference to a cell containing text |
new_text |
Text that replaces “old_text” |
Text string or reference to a cell containing text |
[instance_num] |
(optional) Specifies the number of a match to replace. If the argument is omitted, all occurrences of the old text in the string will be substituted |
Integer ≥1 or a reference to a cell containing a number |
Examples of use
Replacing a word in a text
If cell A1 contains the text “I love apples” and you want to replace “apples” with “pears”:
=SUBSTITUTE(A1, "apples", "pears")
The formula will return “I love pears”.
Replacement with an indication of the occurrence number
If cell A2 contains the text “cat, dog, cat” and you want to replace only the first occurrence of “cat” with “bird”:
=SUBSTITUTE(A2, "cat", "bird", 1)
The formula will return “bird, dog, cat”.
Replace all occurrences
If you want to replace all occurrences of “cat” with “bird”:
=SUBSTITUTE(A2, "cat", "bird")
The formula will return “bird, dog, bird”.
Notes
–If old_text is not found in the string argument, the function will return the original text unchanged.
–The SUBSTITUTE function is case-sensitive, so “cat” and “Cat” will be treated as different strings.
–When searching for the old_text argument, not only whole words are found, but also parts of words containing old_text. For example, when replacing the fragment “differ”, the word “difference” will be replaced.