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.
Objectives
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.
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])Arguments
- 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.
Output
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:
=SUBSTITUTE(A2,"A","123",2)The output will be ABC123BC.
Here are some examples of using the SUBSTITUTE function.
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.
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.
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.
Final Result >
In cell B2, the SUBSTITUTE function returned RR240SQT300R as the final result.
SUBSTITUTE Function Vs REPLACE Function
| SUBSTITUTE Function | REPLACE Function | |
|---|---|---|
| Purpose | Replaces specific text occurrences within a text string. | Replaces a specific portion of text within a text string. |
| Syntax | SUBSTITUTE(text, old_text, new_text, [instance_num]) | REPLACE(old_text, start_num, num_chars, new_text) |
| Replacement | Replaces all occurrences of old_text with new_text, unless instance_num is specified. | Replaces a specified number of characters with new_text. |
| Case-sensitivity | Case-sensitive (distinguishes between uppercase and lowercase). | Case-insensitive (does not differentiate between uppercase and lowercase). |
| Wildcards | Does not support wildcards (* or ?). | Does not support wildcards (* or ?). |
| Nested Functionality | Can be nested within other functions for multiple replacements. | Cannot be nested within itself or other functions (performs a single replacement). |
Availability
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Remarks
- 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.
Conclusion
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!
