Excel SUBSTITUTE Function
The Excel SUBSTITUTE function replaces text in a given string by matching. For example =SUBSTITUTE("952-455-7865","-","") returns "9524557865"; the dash is stripped. SUBSTITUTE is case-sensitive and does not support wildcards.
- text - The text to change.
- old_text - The text to replace.
- new_text - The text to replace with.
- instance - [optional] The instance to replace. If not supplied, all instances are replaced.
The Excel SUBSTITUTE function can replace text by matching. Use the SUBSTITUTE function when you want to replace text based on matching, not position. Optionally, you can specify the instance of found text to replace (i.e. first instance, second instance, etc.).
SUBSTITUTE is case-sensitive. To replace one or more characters with nothing, enter an empty string ("").
Below are the formulas used in the example shown above:
The SUBSTITUTE function cannot replace more than one string at a time. However, SUBSTITUTE can be nested inside of itself to accomplish the same thing. For example, with the text "a (dog)" in cell A1, the formula below will strip parentheses () from text:
This same approach can be used in a more complex formula to normalize telephone numbers.
Use the REPLACE function to replace text at a known location in a text string. Use the SUBSTITUTE function to replace text by searching when the location is not known. Use FIND or SEARCH to determine the location of specific text.
- SUBSTITUTE finds and replaces old_text with new_text in a text string.
- Instance limits SUBSTITUTE replacement a particular instance of old_text.
- When instance is omitted, all instances of old_text are replaced with new_text.
- SUBSTITUTE is case-sensitive and does not support wildcards.