How to Use Excel SUBSTITUTE Function

Last modified date

Click here to download the excel file which contains the below examples

How to Use Excel SUBSTITUTE Function

Description of Excel SUBSTITUTE Function

SUBSTITUTE: This function will help us to replace the whole or part of the old text with new text. This function has additional option compared to REPLACE function, if we have old text in multiple places then we can give an instance to replace particular place like 1st 2nd 3rd or as it is an optional argument if no value is provided by default it will replace all the instances. 

Excel SUBSTITUTE is case -sensitive function and wildcards(*?~) are not allowed.

Syntax of Excel SUBSTITUTE Function
SUBSTITUTE(text,old_text,new_text,[instance_num])
  • 1st Argument (Compulsory): text
  • 2nd Argument (Compulsory): old_text
  • 3rd Argument (Compulsory): new_text
  • 4th Argument (Optional): [instance_num]  (For any Excel Function Syntax if we have square brackets then it is optional)
Arguments description of Excel SUBSTITUTE Function
  • text: 1st Argument here we need to select the cell which has the original text or else update the text in double quotes(“”).
  • old_text: 2nd Argument here we need to select the cell which has the old text or else update the text in double quotes(“”). 2nd argument value will be replaced with new text.
  • new_text: 3rd Argument here we need to select the cell which has the new text or else update the text in double quotes(“”). This new_text will replace the old text mentioned in the 2nd argument.
  • [instance_num]:4th Argument it is optional if you have old text multiple places, then we need to give the which instance to replace. If no value is mentioned then by default it will replace all the instances.
Usage of Excel SUBSTITUTE function
Excel SUBSTITUTE Function
Excel SUBSTITUTE Function Examples
Explanation Excel SUBSTITUTE Function Examples

Example 1: In text “puppy” we want to replace the letter “p” with “d”.

Formula used in Cell H7=SUBSTITUTE(D7,E7,F7)

The result is “duddy”  (As we have not used the 4th argument all the instances are replaced)

Example 2: In text “puppy” we want to replace the letter “p” with “d”. But we want only 1st instance should be replaced.

The formula used in Cell H8=SUBSTITUTE(D8,E8,F8,G8)

The result is “duppy”  (As we have used the 4th argument only 1st instance is replaced)

Example 3: In text “puppy” we want to replace the letter “p” with “d”. But we want only 3rd instance should be replaced.

The formula used in Cell H9=SUBSTITUTE(D9,E9,F9,G9)

The result is “pupdy”  (As we have used the 4th argument only 3rd instance is replaced)

Leave a Reply

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

Post comment