How to use the Excel SUBSTITUTE function

Substitute function in excel is a very useful function which is used to replace or substitute a given text with another text in a given cell, this function is widely used when we send massive emails or messages in a bulk, instead of creating separate text for every user we use substitute function to replace the information.

Syntax: =SUBSTITUTE(text, old_text, new_text, instance_num)

The SUBSTITUTE function syntax has the following arguments:

  • Text (Required) The text or the reference to a cell containing text for which you want to substitute characters.
  • Old_text (Required) The text you want to replace.
  • New_text (Required) The text you want to replace old_text with.
  • Instance_num (Optional) Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

Example: Let’s look at some Excel SUBSTITUTE function examples and explore how to use the SUBSTITUTE function as a worksheet function in Microsoft Excel:

Excel SUBSTITUTE Function 1 - How to use the Excel SUBSTITUTE function

Syntax:  =SUBSTITUTE(A2,B2,C2,D2)

Result:

Excel SUBSTITUTE Function - How to use the Excel SUBSTITUTE function

Based on the Excel spreadsheet above, the following SUBSTITUTE examples would return:

Syntax: =SUBSTITUTE(A3,B3,C3,D3)
Result: Sophuc

Syntax: =SUBSTITUTE(A4,B4,C4,D4)
Result: Excel SUBSTITUTE Function

Syntax: =SUBSTITUTE(A5,B5,C5,D5)
Result: Excel SUBSTITUTE

Syntax: =SUBSTITUTE(A6,B6,C6,D6)
Result: SUBSTITUTE Function

Syntax: =SUBSTITUTE(A7,B7,C7,D7)
Result: SUBSTITUTE function in excel

Note:

  • SUBSTITUTE finds and replaces old_text with new_text in a text string.
  • Instance limits SUBSTITUTE replacement to one particular instance of old_text. if not supplied, all instances of old_text are replaced with new_text.
  • Use SUBSTITUTE to replace text based on content. Use the REPLACE function to replace text based on its location.
  • SUBSTITUTE is case-sensitive and does not support wildcards.

Add a Comment

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