What Does Substitute Function Do in Excel?

Syntax

The SUBSTITUTE function in Excel has four parameters, three (text, old_text,new_text) are compulsory parameters and one (instance_num) is optional.

Compulsory Parameter:

  • text: It is a text from which we want to substitute some text.old_text: It is a text which is going to replace.new_text: It is the text which replaces the old text.

Optional Parameter:

  • [instance_num]: it specifies the occurrence of old_text. if you specify the instance only, that instance will have replaced by a substitute function; otherwise, all instances are replaced by it.

How to Use Substitute Function in Excel? (with Examples)

Example #1

In the first example, we will substitute the “_” with space in the given set of Name data.

To get the desired output, Tanuj Rajput from Tanuj_Rajput apply the Substitute formula SUBSTITUTE(A2,”_,” “1)

It will replace the first instance of “_” with space, and you will get the desired data as output.

Example #2

In this example, we will replace the first instance of character “a” with “w” in the given dataset of full name by using the SUBSTITUTE function.

Let us apply the =SUBSTITUTE(A2,” a”,” w”,1) formula in the “Formula” column:

As shown in the below table, you will get the output in the “Output” column, shown in the third column.

Example #3

In this example, we will replace all instances of character “a” with “w” in the given data set.

And drag it in the “Formula” column to the “Output” with no “a” values, as shown in the below table.

Example #4

In this example, we will replace all the spaces with blank from the given set of full names.

Here, we will apply the below substitute formula to achieve this =SUBSTITUTE(I8,”, “)

You will get the output without space, as shown in the table below.

Things to Remember

  • The substitute function is the case-sensitive function. The SUBSTITUTE function considers tanuj and Tanuj as different values, which means it can distinguish between lower and upper case.Substitute function does not support wildcard characters, i.e. “?” ,“*” and “~” tilde.

This article is a guide to the SUBSTITUTE in Excel. Here, we discuss how to use the SUBSTITUTE function in Excel, along with an Excel example and downloadable Excel templates. You may also look at these useful functions in Excel: –

  • The SUBSTITUTE function considers tanuj and Tanuj as different values, which means it can distinguish between lower and upper case.

  • VBA SubVBA SubSUB in VBA is a procedure which contains all the code which automatically gives the statement of end sub and the middle portion is used for coding. Sub statement can be both public and private and the name of the subprocedure is mandatory in VBA.read moreDSUM FunctionMinimum in ExcelExcel Subtraction FormulaExcel Subtraction FormulaThe subtraction formula in excel facilitates the subtraction of numbers, cells, percentages, dates, matrices, times, and so on. It begins with the comparison operator “equal to” (=) followed by the first number, the minus sign, and the second number. For example, to subtract 2 and 5 from 15, apply the formula “=15-2-5.” It returns 8.read moreHow to Create an Excel Spreadsheet?How To Create An Excel Spreadsheet?To create an excel spreadsheet, do the following: 1.Open MS Excel 2.Select New from the Menu dropdown list 3. Click the Blank workbook button to start a new worksheet. The keyboard shortcut for this is Ctrl + N.read more