SUBSTITUTE is case-sensitive. To replace one or more characters with nothing, enter an empty string ("").

Examples

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.

Notes

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.

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.