How to Use SUBSTITUTE Function in Excel

Excel SUBSTITUTE Function can be used to replace one or more instances of any specific character or substring in a give text string. The function is case-sensitive and does not support wild-card characters as well.

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

The Function takes four arguments out of which first three are necessary. You first need to supply the string in which you want to replace your "old_text", 2nd argument with 3rd argument "new_text". If 4th argument is not supplied, all instances of "old_text" will be replaced with "new_text".

You can also use this function to remove all spaces, removing special characters etc. This can also be used to "Count and Return the available number of words as in particular cell or range of cells". I have explained the use of the same by using LEN, TRIM, SUBSTITUTE functions.

[melissakish]

What formula does he have in column F that makes the formula text appear after he enters it in column E?

[akashsingh-zbur]

Can we replace a value by *** in excel by this function

[bivishan]

I don't understand properly, can u say in slow motion?? :/