What is the use of SUBSTITUTE function?

SUBSTITUTE Function in Excel replaces old text with new text, in a text string or cell reference. It will replace all instances of the old text, unless a specific instance number is selected.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Ultimate Project Management Template
Excel Template

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


What is the syntax of SUBSTITUTE function?

SUBSTITUTE Function in Excel - Syntax

SUBSTITUTE(text, old_text, new_text

[,instance_num])
Where
text : The text for which you want to substitute characters.
old_text: The text you want to replace or remove.
new_text: The text you want to replace old_text with.
instance_num: This is optional parameter. The specific occurrence of old text that you want to replace.

Note:1.The “SUBSTITUTE” function is case sensitive.
2. If you specify “instance_num”, Only that instance of “old_text” will be replaed.
3. If “instance_num” is left blank, It will substitute(replace) every occurrence of”old_text” is changed to “new_text”.

SUBSTITUTE Function in Excel – Examples

SUBSTITUTE Function in Excel- Example 1

Example 1: Replacing underscore(_) with space
Example 2: It will replace the text in first Instance
Example 3: It will replace the text in second Instance
Example 4: Its replaces every occurrence of “w” is changed to “p”
Example 5: Its replaces the Space character in the text

Reference:

Please refer the below article for more Text Excel functions.
Text Excel Formulas

Please refer the below article for more Excel Functions.
Excel Formulas | Home

LIMITED TIME OFFER - Get it Now!
Advanced Project Plan Excel Template

 
Related Resource External VBA Reference