Skip to content

How to Use Excel Substitute Function?

Ads
Listen to this article
Substitute FunctionPin
Substitute Function

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

Excel SUBSTITUTE FunctionPin
Excel SUBSTITUTE Function

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.
Input ValuePin
Input Value
  • After that, you have to apply the above-mentioned formula, as shown in the below image.
Enter the formulaPin
Enter the formula
  • To get the result, you will need to click on the Enter button.
OutputPin
Output
  • 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.
ValuesPin
Values
  • 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.
Input RangePin
Input Range

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.

Video Tutorial

This video will show you how to use the SUBSTITUTE Function in the Excel spreadsheet.

Related Articles:

Tags:

Leave a Reply

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

Share to...