How to Use Excel Substitute Function?

Ads

Listen to this article

Substitute Function in Excel: We all know that Excel is one of the powerful spreadsheet programs that helps to perform various mathematical calculations with a few clicks. It has built-in functions and formulas for doing the operations. Sometimes, we want to replace the existing text with another text, doing this manually will take a longer time.

Excel also provides a replacing feature called “Substitute function.” It helps to replace the existing content with another specified value in the spreadsheet. Here, we will explore the syntax and usage of the Substitute function in Excel with some examples. Get the official version of MS Excel from the following link:

https://www.microsoft.com/en-in/microsoft-365/excel

Explanation of Substitute Function

It is one of the built-in functions in Excel.
It can replace existing text with new text in the specified input string.
You can use it to replace text based on its content, not position.

Syntax

Here, you will see the syntax of the SUBSTITUTE function.
To apply this function on your spreadsheet, you have to select a cell and enter the formula in the following format.
Once you enter the formula, just click on the Enter button to get the result.

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

Argument Explanation:

Text: The input text to change and replace with new text.

Practical Examples

This portion will show you a few examples to make you understand how this SUBSTITUTE function works on your spreadsheet.

Initially, you have to open your Excel workbook on your PC and launch a worksheet that has data.
For example, we have entered the input text in Cell B3, and we will replace the first letter of this text string with another alphabet.

After that, you have to apply the above-mentioned formula, as shown in the below image.

To get the result, you will need to click on the Enter button.

In the following image, you can see the range of input values. We will replace some values in each text string with the help of this Substitute function.

Once you get the output value in the first cell, then you just need to drag down it to fill the remaining cells, as shown in the below image.

Verdict

We hope that this post explained to you clearly about the use of Substitute Function in Excel with some practical examples. Don’t forget to share your feedback in the below comment section. To learn more about Excel functions, then visit our webpage Aawexcel.com.