Excel SUBSTITUTE Function
The Microsoft Excel SUBSTITUTE function replaces text or characters within a text string with another text or characters.
The text after substituted
Example 1: Substitute all instances of a text with another
The SUBSTITUTE function can help to replace all instances of a specific text with a new text.
As the below example shown, you want to substitute all number “3” with “1” in B2 cell, please select a cell to output the result, copy the below formula into the Formula Bar and press the Enter key to get the result.
- 1) In this case, the [instance_num] argument is omitted, so all the number 3 are substituted with 1.
- 2) Keep the new_text argument empty, it will remove the old text and replace with nothing. See the below formula and screenshot:
Example 2: Substitute specific instance of a text with another
As the below screenshot shown, you only want to substitute the first instance of “1” with “2” in B3, please select a blank cell, copy the below formula into it and press the Enter key to get the result.
Note: Here the [instance_num] argument is 1, which means the first instance of “1” will be substituted. If you want to substitute the second instance of “1”, please change the [instance_num] argument to 2.