Substitute Function in Excel

Substitute function in Excel replaces any text or number into another text or number.

In simple words, if you want to remove some text or number from the cell, you can remove it or you can easily replace it.


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

Parameters Exaplained:

  • text - The text you want to change
  • old_text - In the selected text which text to replace.
  • new_text - The new text you want to replace with.
  • instance - Optional, the instance to replace.

Substitute Function Examples

In the below example, you can see a name with spaces in between two texts.

Substitute Function In Excel

you can also remove space using a TRIM function in Excel. but this function will not remove all spaces in between the texts. but using a substitute excel formula we can remove or replace those spaces.

To replace spaces in between the texts follow the below steps.

1. First, use the Substitute function in cell B4 and select the arguments. (which we discussed in the syntax part).

Substitute Excel Formula

2. After applying arguments with the formula you will get the below result.

Substitute Function Result

Result is

Here the applied formula is=SUBSTITUTE(B2," ",""). which means this formula is replaced " " (spaces) into "" (blank).

3. To apply percentage format go to Home Tab, Number Group then click on percentage symbol.

Substitute Example

Let us take one more example.

Here you can see a name with a hyphen (- -) in between two texts.

Substitute formula example

Now we need to replace hyphen (- -) into ** using substitute function.

Substitute formula Result

Formula applied here is =SUBSTITUTE(F2,"-"," * "). which means this formula is replaced " - - " (hyphens) into " * * ".

This is a simple way to use the SUBSTITUTE function in Excel.


Get Your FREE Excel Shortcut Keys e-BOOK