SUBSTITUTE function. Substitutes the specified text.(Microsoft Excel)

03/26/2024

Japanese version.

The SUBSTITUTE function searches for a string and replaces it if a match is found.

How it works

=SUBSTITUTE(Text, Old_text, New_text, Instance_num)

NameOmissionExplanation
TextRequired argument.
Target text.
Old_textRequired argument.
Text to search. Search within a string.
New_textRequired argument.
Text to replace. The text to replace the search string in the string.
Instance_numReplace all.Specifies which Old_text to replace. Specifies numerically how many match results.

Demonstrate

RowPattern
3Replace all search results.
4Replace only the third result.
5There 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.

FunctionReplacement location
SUBSTITUTEText.
REPLACEStart position and number of characters.

---

Links

Microsoft Excel Functions Text

Examples of Use

Formulas to remove multiple types of characters from cell text.