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

Comments

Post a Comment

jeetexceltips@gmail.com

Popular posts from this blog

50 Excel VBA Oral Interview Questions

Ques 01. What is the difference between ByVal and ByRef and which is default ? Solution: ByRef: If you pass an argument by reference when calling a procedure the procedure access to the actual variable in memory. As a result the variable's value can be changed by the procedure. ByVal: If you pass an argument by value when calling a procedure the variable's value can be changed with in the procedure only outside the actual value of the variable is retained. ByRef is default: Passing by reference is the default in VBA. If you do not explicitly specify to pass an argument by value VBA will pass it by reference. Ques 02. What is the meaning of Option Explicit and Option Base? Solution: Option Explicit makes the declaration of Variables Mandatory while Option Base used at module level to declare the default lower bound for array subscripts. For eg. Option Base 1 will make the array lower bound as 1 instead of 0. Option Base - When an array is declared by d...
Read more