SUBSTITUTE function. Substitutes the specified text.(Microsoft Excel)
The SUBSTITUTE function searches for a string and replaces it if a match is found.
Contents
How it works
=SUBSTITUTE(Text, Old_text, New_text, Instance_num)
| Name | Omission | Specify |
|---|---|---|
| Text | Required argument. | Target text. |
| Old_text | Required argument. | Text to search. Search within a string. |
| New_text | Required argument. | Text to replace. The text to replace the search string in the string. |
| Instance_num | Replace all. | Specifies which Old_text to replace. Specifies numerically how many match results. |
Example Results
| Row | Pattern |
|---|---|
| 3 | Replace all search results. |
| 4 | Replace only the third result. |
| 5 | There is no match. |
If there is no match, the text will remain the same instead of an error.
How to Replace Multiple Strings
The SUBSTITUTE function only works with one text.
If you want to replace multiple texts, you must stack multiple functions.
Spill
If any argument is a cell range, the result of the function will be Spill.
In many cases, "Text" will be set to Spill.
Difference from REPLACE function
It is similar to the REPLACE function, which also replaces text, but differs in the selection of replacement locations.
| Function | Replacement location |
|---|---|
| SUBSTITUTE | Text. |
| REPLACE | Start position and number of characters. |
---