Function: SUBSTITUTE

The Excel SUBSTITUTE function is a Text formula that substitutes a text string with another. In this guide, we’re going to show you how to use the Excel SUBSTITUTE function, and also go over some tips and error handling methods.

Supported versions

All Excel versions

Excel SUBSTITUTE Function Syntax

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

Arguments

text	The string where you want to do the substitution.
old_text	The text you want to substitute.
new_text	The text you want to substitute the old_text with.
[instance_num]	Optional. The instance of old_text you want to substitute. If omitted, all instances of old_text will be replaced with new_text.

Examples

Substitute all instances

Omit the [instance_num] argument to replace all instances of the old_text. In our example, we want to replace "Power" with "Force". The following formula replaces both instances of the text "Power".

=SUBSTITUTE("By the Power of Grayskull… I Have the Power!","Power","Force")

Substitute specific instance

If we set [instance_num] to 2, in our example, the SUBSTITUTE function only replaces the 2^nd instance of the text "Power".

=SUBSTITUTE("By the Power of Grayskull… I Have the Power!","Power","Force",2)

Download Workbook

Summary and Tips

Use the REPLACE function to replace any text in a specific location. For example, to replace a 5 character text starting from the 4^th character:

=REPLACE(old_text,4,5,new_text)
The Excel SUBSTITUTE function is case-sensitive.
To remove a specific character or text, replace it with an empty string ("").

=SUBSTITUTE(text," ","")
Use the SUBSTITUTE function in a nested form to substitute different characters with a single formula. For example, to remove space and dash characters, use:

=SUBSTITUTE(SUBSTITUTE(text," ",""),"-","")