- 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.
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.