Explanation

The SUBSTITUTE function allows us to replace a character (or a number of characters) with another character (or a number of them) within a certain text expression or cell. 

Example

Let’s say we want to replace the word “fox” with the word “cat” in the following sentence: “The quick brown fox jumps over the lazy dog”:

Syntax

=SUBSTITUTE(text,old_text,new_text,[instance_num])

text – The text in which we want to perform the substitution. It could be a single character, word, sentence or reference to another cell

old_text – What we are looking for to replace. Could be a single character or multiple.

new_text – What we wish to insert into the text as a replacement. Could be a single character or multiple.

[instance_num] – Optional. If a certain character/word appears multiple times in our text and we wish to replace it only in a specific instance, we can use this argument. If we don’t use it, all instances will be replaced with the new_text. 

Reminder 1– If you are typing actual text within any of the arguments of the function, don’t forget to wrap it with quotes. 

Tip – SUBSTITUTE function is case-sensitive. This means that we need to be careful and make sure we use (or don’t use) capitalization where needed. Substitute of “d” is not the same as Substitute of “D”!

Practice SUBSTITUTE function

OK, time to practice 🙂