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


vlookups on exceldynamic cell reference excelproject checklist template excelsql compare operatorsmicrosoft excel quote templateexcel functions and syntaxvba list files in folderexcel vba paste rangehow to analyse trends in excelexcel macro paste valuesexcel vlookup trueaccess vba if statementexcel vba open csv fileexcel gotomicrosoft excel gantt chart templateproject management templates for excelhow to remove the duplicates in excelvba create classexcel form vbaexcel worksheet rangehow to join rows in excelsurface graph excelvba run commandsas tutorial pptfiledialog filterinterview question and answer for fresherexcel consolidationinsert checkbox in excel 2007access vba export to excelexcel 2010 developer modesort macro exceldelete rows excelvba counta functionunshare a shared workbookcopy worksheet vbaexcel vba sample projectsexcel vba cellsvba rgb colorsql certification questions and answers pdfsql ddl and dmlif iserror vbabest excel book for data analysissave xlsm as xlsxhow to find duplicate values in excel 2010excel vba close filevlookup formula in excelsql queries interview questions and answers pdf free downloadhow to create a command button in excelword userform examplesvalue vba excelinsert check mark in excel 2007alphabetize column in excelexcel comparison operatorscapacity planning excel template freeexcel macro copy cell to another sheetmacro security in excel 2007remove duplicate entries in excelvba code to save excel filevba concatenationhow do you create a filesystemobjectscrum meeting templateexcel vba if statement multiple conditionsvlookup and hlookup tutorialexcel vba hyperlink text to displayexcel if statementspivot tables in excel 2003count cells in a range vbapassword protect a spreadsheetexcel vba tutordatediff excelunlock excel 2007 passwordwhat is the vlookup function in excelexcel vba offsetclose form vbaaptitude and technical test questions and answers