Substitute Formula
 3 Comment
The substitute formula is an relatively simple formula that can come in handy in many situations. I like to use this formula the most when a longer text field needs just a portion of it changed, without having to retype the entire cell value. Let’s go ahead and take a look at the substitute formula:
=SUBSTITUTE(Text, Old_text, New_text, Instance_num)
Looking at each of the criteria of the field, we’ll see how this formula is formed. Text is the string or cell reference of the string you want to change. Old_text is the value inside the Text string that needs changed. New_text is the value you want to change to the Old_text value to. And finally Instance_num is only specified if you only want to change a certain occurence of the the Old_text. If you don’t specify and Instance_num, all instances of Old_text will be replace.
Alright, let’s go ahead and take a look at some examples of how the Substitute Formula works.
EXAMPLES
Example 1: =substitute(“Let’s have fun.”, “fun”, “dinner”) will return “Let’s have dinner.”, since “fun” is replaced by “dinner”.
Example 2: =substitute(“01 02 03 03 02″, “02″, “AB”) will return “01 AB 03 03 AB”, since “02 is replaced twice by the new text string “AB”
Example 3: =substitute(“123454321″,”2″,”x”,2) will return “1234543×1″, since the second instance of “2″ is replaced by “x”.
Using the spreadsheet above, you can see how the formula can quickly be used to replace instances where changes are needed.
Example 4: =substitute(A1, “2008″, “2009″) will return “The year is 2009″.
Example 5: =substitute(A3, “It’s”, “It’s not”) will return “It’s not 2008″.
As you can see, especially with long strings where retyping the entire string would be tedious, the substitute formula can come in handy in many situations. If you had an example like the ones above, where you had to replace the same string 20 times in one cell and do it thousands of times, it can save hours of work for both you and your company.
As always, please feel free to leave your comments below and make further suggestions for future updates to Excel Hints.
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.
More Excel Tips
3 Comments on this post
Trackbacks

neel raj said:
i run into a issue using this formula. for example say cell A1 has the following text “45 people failed the test 4 times” i want to remove test 4, but the issue is the number 4 keeps changing sometime its 5 sometimes its 1, so one day cell a1 will say “45 people failed the test 4 times” and sometimes cell a1 will say “45 failed the test 5 times”,
i used the substitute(a1,”test #?,””) but that came back with a error
September 29th, 2010 at 10:43 pm 
Ramaki said:
Please enter the formula shown below
SUBSTITUTE(C7,4, ” “) result ” 5 people failed the test times”
July 6th, 2012 at 12:03 am 
Warren said:
How can I get Excel to list out 7 numbers. For example, if I have 11 numbers and I can only have 7 of them be active at any time, but I want the 7 that are active to be randomly and evenly used, how would I do that?
November 10th, 2012 at 11:19 pm