Substitute Formula

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 theText 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 ofOld_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”.