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 its content, not position. Optionally, you can specify the instance of found text to replace (i.e. first instance, second instance, etc.).
Below are the formulas used in the example shown above:
Notice SUBSTITUTE is case-sensitive. To replace one or more characters with nothing, enter an empty string (""), as in the last example.
- SUBSTITUTE finds and replaces old_text with new_text in a text string.
- Instance limits SUBSTITUTE replacement to one particular instance of old_text. if not supplied, all instances of old_text are replaced with new_text.
- Use SUBSTITUTE to replace text based on content. Use the REPLACE function to replace text based on its location.
- SUBSTITUTE is case-sensitive and does not support wildcards.