Leading the way in Microsoft Office Development
 Home|Excel|Word|PowerPoint|Consultancy|Feedback|Contact 
 Microsoft Excel > Functions > Text > SUBSTITUTE
 

SUBSTITUTE(text, old_text, new_text [,instance_num])

 
 Replaces a sequence of characters with a different sequence of characters in a text string.

 textThe text for which you want to substitute characters.
 old_textThe text you want to replace.
 new_textThe text you want to replace old_text with.
 instance_numThe occurrence number you want to replace.

 REMARKS
 
  		• This function should be used when you want to replace all the instances of some text regardless of their location in the text.
     
  		• The "text" can be a cell reference.
     
  		• If you specify "instance_num", only that instance of "old_text" is replaced.
     
  		• If "instance_num" is left blank, every occurrence of "old_text" is changed to "new_text".
     
  		• This function is very similar to the REPLACE() function.

     EXAMPLES
     
     A
    1=SUBSTITUTE("Sales Data","Sales","Cost") = Cost Data
    2=SUBSTITUTE("Quarter 1, 1991","1","2",1) = Quarter 2, 1991
    3=SUBSTITUTE("Quarter 1, 1991","1","2",3) = Quarter 1, 1992
    4=SUBSTITUTE("this is the old text","old","new",1) = this is the new text
    5=SUBSTITUTE("CellCont2","named_range","] [") = CellCont2
    		 
     Functions - S | Index - S | Text Manipulation | Office Online 

     Copyright © 2004-2007 Better Solutions Limited. All Rights Reserved.Top