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


excel vba stringexcel vba commentdo while loop vba exampleclose excel in vbaunhiding column asumif examplesdonut chart generatorexcel formula chartshortcut formulas in excelhow to protect excel from editingexcel bubble plotvba find last row with datavba copy to clipboardsave as filename vbacountif syntaxdelete a row vbacreate input form in excel 2010excel macro vlookupexcel select duplicateslast cell in column vbaexcel conditional rangeexcel formula vlookupdelete macro excelapplication statusbarhow to find duplicates in excel sheetinsert a new worksheet in excellearn excel torrentsort in ascending order excelunmerge cell exceldifferent types of languages in sqlsumif excel criteriapivot table example in excel 2007what are dml commandsvba close filehow to make a pivot table in excel 2010alternative to vlookupexcel 2010 name managerexcel construction templatesvba userform listboxexcel vba create charthow to use datevalue function in excelworkbook.worksheetsexcel basics tutorialcalling functions in vbaexcel coding tutorialvba unprotect all sheetssql server ddl commandsvlookup excel step by stepvba excel do while looptick sheet excelproject delivery plan templateproject delivery plan templatepivot table descending orderhow to delete duplicate records in excelhlookup excel 2010lookup function examplein vba codems excel hlookup exampleadvanced if statements in excelexcel hyperlink to filecountifs formula in exceltechnical interview questions and answers for freshers pdfwriting vba codeexcel dashboard widgetsexcel vba pivot tablevba unprotectvb6 carriage returnvb programming examplesexcel macros buttoncountifs function excel 2010vba excel dashboardremove macro excelhow to build dashboards in excelmicrosoft excel 2010 pivot table tutorialdelete duplicate in excelif statements in excel vbahow to change row height in excelsql server basic commandsrca format in wordbubble chart excel templatesyntax bold font