SUBSTITUTE Function - Excel
Overview
The SUBSTITUTE function in Excel is a useful tool for replacing a specified substring or characters within a text string with another set of characters. This function provides an easy way to replace text within a given string. This function is case sensitive.
Example:
=SUBSTITUTE("the dog is running", "dog", "cat")
Syntax:
=SUBSTITUTE(text, old_text, new_text, [instance_num])
text: This is the text string containing the characters you want to replace.
old_text: This is the substring or characters within text that you want to replace.
new_text: This is the replacement substring or characters.
[instance_num]: (Optional) This parameter allows you to specify which occurrence of old_text to replace. If omitted, all occurrences are replaced.
Example
In this example, the SUBSTITUTE function is used to replace the word “dog” in the text string “The dog is running” with the word “cat”. The final output of the function is “The cat is running”.
Example: instance_num
In this example, I will show how we can use the optional instance_num input for the SUBSTITUTE function. Here we have the string “dog dog dog” and we want to replace the word “dog” with the word “cat” inside that string. However, we only want to replace one instance of the word “dog”. Using the optional instance_num input, we give it a value of 2 so that it only replaces the 2nd instance of the word “dog” with “cat”. Our final output is “dog cat dog”.
