Excel’s SUBSTITUTE function allows us to substitute part of a string with another part:

SUBSTITUTE(String,”Old Text”,”New Text”,occurrence”)

Where:
• String is the string that we are working with
• “Old Text” is the text that we want to eliminate
• “New Text” is the text that we want to incorporate
• Occurrence is which instance of the old text we wish to replace

Lets consider the following 3 examples:

Looking at cell B3, we want to replace the word “sick” with “large”. We can use the following expression:

=SUBSTITUTE(B3,”sick”,”large”,1)

If the text that we want to replace occurs more than once then we need to specify the instance that we mean. In the second example, we have two occurences of the word “old”. So if we want to change second instance to the word “grey” then we have to use:

=SUBSTITUTE(B4,”old”,”grey”,2)

Note that if the occurrence is not specified then all instances are replaced. So:

=SUBSTITUTE(B4,”old”,”grey”)

Would get rid of all instances of the word “old” and replace them with the word “grey”. Note that if the old text cannot be found then the string is unchanged. So looking at the last example:

=SUBSTITUTE(B5,”black”,”grey”,1)

Means that we try and replace the word “black” with the word “grey”. However, as “black” does not occur the original string remained unchanged:

To download the .XLSX file from this article, click here