An Overview of SUBSTITUTE Function | Microsoft Excel

The SUBSTITUTE function is one of the text functions in Microsoft Excel. You can use it as a standalone function as well as in collaboration with the other functions. This article will give you an overview of the SUBSTITUTE function in Excel.


The SUBSTITUTE function can replace one or more text strings with new text strings. The SUBSTITUTE function falls under the text function category. It is available in Microsoft 2007 and later versions.


=SUBSTITUTE(text, old_text, new_text, [instance_num])


  • text: Compulsory. The text string from which you need to extract the characters
  • old_text: Compulsory. It is the mention of the character(s) that you want to change.
  • new_text: It is the mention of the character(s) that you want to be replaced with the old_text. It is also a compulsory argument.
  • instance_num: Optional argument. Use instance_num if the character you want to replace appears more than once in the text string. The new_text will be used instead of the old_text if you do not mention the intance_num of the old_text.


The function will return as mentioned in intance_num. For example, if you have the text string: FLOOR and it has two O‘s. Now you want to replace the second ‘O’ with a ‘U’. So, mention ‘2’ in the formula as the instance_num.

Example of SUBSTITUTE Function

Assume, you have a text string that is ABCABC, and the text string is located in cell A2. Now you want to replace the second A in the text string, and in its place, you want to substitute the number 123. To do that, you can use the SUBSTITUTE function. Here is the formula using the SUBSTITUTE function:


The output will be ABC123BC.

Here are some examples of using the SUBSTITUTE function.

Example of SUBSTITUTE Function in Excel

Usage Guide of SUBSTITUTE Function in Excel

To show you how to use the SUBSTITUTE Function in Excel, I have an example text string in cell B1: RR240R300R.

Follow these steps to understand how to use the SUBSTITUTE function.

Usage Guide

Step_1:  Start by inserting an equal sign (=) in a blank cell, B2.

Step_2: Then type SUBSTITUTE.

Step_3: Insert an open parenthesis (.

As you type the open parenthesis (, Excel will show the syntax of the SUBSTITUTE function.

Applying The SUBSTITUTE Function in Excel

Step_4: Insert the text argument.

Here, I’m inserting B1, which is the cell address that contains the text.

Step_5: Then insert a comma (,).

After inserting the comma (,), Excel will ask you for the old_text argument.

Step_6: Now insert the old_text inside double quotation marks (“ “).

I typed “R”.

Step_7: Again, insert a comma (,).

Step_8: Type the new_text inside double quotation marks (“ “).

I’m inserting “SQT” as the new_text.

Inserting arguments in the SUBSTITUTE Function in Excel

Step_9: Put a comma (,).

Step_10: Insert the instance_num argument.

As I wanted the third R to be replaced with SQT, I inserted 3 as the instance_num.

Process to Use the SUBSTITUTE Function in Excel

Final Result >

Result of Using the SUBSTITUTE Function in Excel

In cell B2, the SUBSTITUTE function returned RR240SQT300R as the final result.


PurposeReplaces specific text occurrences within a text string.Replaces a specific portion of text within a text string.
SyntaxSUBSTITUTE(text, old_text, new_text, [instance_num])REPLACE(old_text, start_num, num_chars, new_text)
ReplacementReplaces all occurrences of old_text with new_text, unless instance_num is specified.Replaces a specified number of characters with new_text.
Case-sensitivityCase-sensitive (distinguishes between uppercase and lowercase).Case-insensitive (does not differentiate between uppercase and lowercase).
WildcardsDoes not support wildcards (* or ?).Does not support wildcards (* or ?).
Nested FunctionalityCan be nested within other functions for multiple replacements.Cannot be nested within itself or other functions (performs a single replacement).


  • Excel for Microsoft 365
  • Excel 2016
  • Excel for Microsoft 365 for Mac
  • Excel 2016 for Mac
  • Excel for the web
  • Excel 2013
  • Excel 2021
  • Excel 2010
  • Excel 2021 for Mac
  • Excel 2007
  • Excel 2019
  • Excel for Mac 2011
  • Excel 2019 for Mac
  • Excel Starter 2010


  • You can nest SUBSTITUTE functions within each other to perform multiple replacements. However, be mindful of the order in which the functions are nested, as Excel evaluates them from the inside out.
  • The SUBSTITUTE function does not modify the original text. It returns a new text string with the replacements made.
  • If you need to make multiple replacements within a single text string, consider using the SUBSTITUTE function in combination with other functions like CONCATENATE or the ampersand (&) operator.
  • The SUBSTITUTE function does not support wildcard characters like asterisks (*) or question marks (?). It only replaces exact matches of old_text.
  • The SUBSTITUTE function has a limit of 32,767 characters for the combined length of text, old_text, and new_text arguments.


The SUBSTITUTE function is one of the most commonly used functions from the text category in Excel. I hope you find the SUBSTITUTE guide helpful. Also, if you have any confusion or questions regarding the SUBSTITUTE function, leave a comment in the comment box. Thank you!

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *