How to use the SUBSTITUTE function in Microsoft Excel

Let's look at some Excel Substitute function examples and explore how to use the Substitute function as a worksheet function in Microsoft Excel:

Summary

The SUBSTITUTE function is as similar to the REPLACE function used in the excel. However, SUBSTITUTE function replaces one or more instances of a substituted text string with a specified character(s). Whereas REPLACE function only replaces the text in a specified position/location of a supplied string.
Substitute function can be useful for when you want to substitute old text with a new text in the given text. For example, if the given text is 9988-776-655 and you want it as 9988776655 then you can use SUBSTITUTE function to transform this.
The SUBSTITUTE function is a built-in function in Excel that is categorized as a Text Function.

What Does It Do?

Replace text based on content.

Return value

The string/text Value.

Syntax

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

Parameters or Arguments

text - The original string to use to perform the substitution.
old_text - The text to replace.
new_text - The new text to replace with.
instance_num - The instance to replace. If omitted, every occurrence of the old text will be changed to the new text.

Formatting

No special formatting is needed.

Substitute different characters with a single formula

You can nest several SUBSTITUTE functions within a single formula to do several substitutions at a time, i.e. substitute multiple characters with a single formula.

Below are the example of this.

Usage notes

• The Excel Substitute function can replace text by matching. Use the Substitute function when you want to replace text based on its content, not position. Optionally, you can specify the instance of found text to replace (i.e. first instance, second instance, etc.).
• Substitute function finds and replaces old_text with new_text in a text string.
• If you specify an instance number, then only that instance of the old text is replaced. Otherwise, every occurrence of old text in the text string is changed to new text.
• Use SUBSTITUTE function to replace text based on content. Use the REPLACE function to replace text based on its location.
• Substitute function is case-sensitive.
• Substitute function does not support wildcards.

Click here to learn more excel function