SUBSTITUTE

Today in this article I will discuss about SUBSTITUTE function in Microsoft Excel.

As the name of this function indicates, SUBSTITUTE is used to replace an old text by new text within a text or string, for example, suppose in any cell a text is written as “Excel” now you want to replace the letter ‘x’ by ‘Y’; this change can be done using SUBSTITUTE function. You can also find and replace cell reference in excel formula or find and replace part of  formula in excel.

SUBSTITUTE function, if used with some other function(s) i.e., nested it can solve difficult tasks which I will show you in my next article.

Let us discuss about SUBSTITUTE function in detail. In my next article I will discuss how to use SUBSTITUTE function. Generally this function is used to replace a particular character or number or special character.

Syntax

The syntax for the SUBSTITUTE function in Microsoft Excel is...

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

Parameters or Arguments

text
The original string to use to perform the substitution operation.

old_text

The existing characters to replace.

new_text

The new characters to replace old_text with.

instance_num

Optional. It is the nth appearance of old_text that you wish to replace. If this parameter is omitted, then every occurrence of old_text will be replaced with new_text.

Returns

The SUBSTITUTE function returns a string/text value.

Note : SUBSTITUTE function is case sensitive. This function first searches for the provided text and then if found it replaces that character or string with the new text. Therefore, while writing this formula, please do not change the case of the alphabets otherwise this function will not work or it will give you an error or the replacement will not be proper. 

Example :

How to use SUBSTITUTE function?

As shown in the below picture, let suppose I have a text as  “Excel-Solutions-Basic-and-Advanced” and I have to replace the dash(-) with a blank space, it can easily be done using “Find and Replace” option in Microsoft Excel by pressing Ctrl+F but using a function is wonderful.

How to use SUBSTITUTE function in Excel formula

So, the excel formula of SUBSTITUTE function will be =SUBSTITUTE(B3,"-"," ").

Here SUBSTITUTE function omits the DASH (-) and inserts a blank space and the resultant text now is "Excel Solutions Basic and Advanced".

Hope you have enjoyed this article, in my next article I will show you how beautifully we can use this very basic function to resolve difficult situations.

Thank you for reading…