SUBSTITUTE Function in Excel

Home/Excel Formulas/SUBSTITUTE Function in Excel

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

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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
By |April 27th, 2013|Excel Formulas|0 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

Leave A Comment


Related pages


vba pop up windowadvanced excel macropl sql for dummieshow do i hide a column in excelcolor index excelsql statement in vbaexcel pivotingexcel vba columnistext excelhow to unlock password excel fileexcel conditional rangehow to combine multiple excel worksheets into onecreate a chart from selected range of cells in excelhow to unhide columns on excelquery excel with sqlpassword protect excel workbookhow to do multiple if statements in excelhow to select different rows in excelworkbooks activateexcel vba copy and pastevba programming basicsexcel vba controls collectionmacros in excel 2007how to deduplicate in excelhow to create a spider web chart in excelclassification of sql commandsexcel vlookup formulasexcel vba programming booksvba active workbookvlookup with exampleexcel 2010 merge cellsvba add hyperlinkexcel hide worksheethow do you merge cells in excel 2013how to use averageif function in excel 2010vb exit forexcel eliminate duplicatessimple vbscript exampleschedule template powerpointexcel combo box multiple selectionsmacros in databaseexcel call macroadvanced filter criteria rangeexcel macro sheetjob costing excel templatehow to collect data from multiple excel filesvba sort columniferror function in excel 2010how to connect excel to sql server databaseexcel macro commandsexcel chart tutorialvba run commandvba excel functionadvance excel tutorialprotecting worksheets in excelhow to unlock excel file passwordworkbooks.activatesql select from excel sheetexcel formula colour cellcommand button vba exceluse of sumifwhat are the ddl commandsexcel 2010 hyperlink functionhyperlink shortcut in excelvba showalldatams excel 2007 shortcut keys listexcel macro change cell colorexcel vba xldownvba excel countifexcel duplicate entriesinsert text box excel