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.
- All Excel versions
Excel SUBSTITUTE Function Syntax
The string where you want to do the substitution.
The text you want to substitute.
The text you want to substitute the old_text with.
Optional. The instance of old_text you want to substitute. If omitted, all instances of old_text will be replaced with new_text.
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 specific instance
If we set [instance_num] to 2, in our example, the SUBSTITUTE function only replaces the 2nd instance of the text "Power".
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 4th 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," ",""),"-","")