| Be My Friend

Substitute Function in Excel

Substitute function in Excel | Substitute Function Syntax | Substitute Function Examples | Download the Sample File

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.

Syntax

=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.

You May Also Like the Following Excel Tutorials:

IF YOU LIKE OUR EXCEL TUTORIALS PLEASE DON'T FORGET TO SHARE

Subscribe for more videos on our Youtube channel. if you like our tutorial please Like and Subscribe to our Facebook and Youtube.

Download the sample Excel SUBSTITUTE Function file for free.

To Get FREE Excel Shortcut Keys
       



Previous

Next



YOUR COMMENTS ARE MOST IMPORTANT TO US:

0 Comments




Why basictutorials.in?

To Learn:

Tutorials in a simple Way.

Excel with downloadable samples.

HTML with sample website code.

PowerPoint presentations.

MS Word for offical use.

Useful Tutorials