We have a report and we want to change a portion of the text after it has already been placed in Excel. It is quite problematic using the formulas but thanks to SUBSTITUTE FUNCTION which is very simple yet powerful to perform this cumbersome task in fraction of seconds.
As SUBSTITUTE FUNCTION is concerned with the text, it is present under the TEXT FUNCTION CATEGORY of the functions.
SUBSTITUTE FUNCTION SIMPLY SUBSTITUTES [REPLACES] ANY TEXT IN AN OLD TEXT WITH A NEW ONE WITH THE CONTROL OF REPLACING ALL THE OCCURRENCES OR ANY SPECIFIC ONE.
For the sake of understanding, let us take an example.
Suppose, we have a sentence ” My name is xyz.” Now if we want to substitute xyz. with a new name abc, we can do so easily with the help of this function.
In this article we would learn about the purpose, syntax, formula of the SUBSTITUTE FUNCTION and get a better understanding with the help of the examples.
PURPOSE OF SUBSTITUTE FUNCTION IN EXCEL
SUBSTITUTE FUNCTION is used to search any character or text fragment within a longer text and return its starting number.
If we have a text snippet ” David feels good”
Suppose we want to substitute this name with another name say Abraham.
One method is that we can change it manually and the smarter solution is to use this function to substitute this name.
When we have to handle big data like hundreds of names, we can make use of this function.
PREREQUISITES TO LEARN SUBSTITUTE FUNCTION
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
Basic understanding of how to use a formula or function.
SUBSTITUTE(TEXT, TEXT TO BE CHANGED, NEW TEXT, INSTANCE NUMBER)
TEXT The text in which we want to perform the substitution.
TEXT TO BE CHANGED Text which we will find in the TEXT and substitute. [ change]
NEW TEXT The new text with which we will replace the TEXT TO BE CHANGED.
INSTANCE NUMBER [OPTIONAL] This is the occurrence number of the text to be substituted in the given TEXT. For example the TEXT can have many instances of the text which we want to replace. So this option is very important and gives us the option of choosing the instance which we want to replace.
All these options will become more clear through the examples which we’ll discuss later in this article.
EXAMPLES:SUBSTITUTE FUNCTION IN EXCEL
EXAMPLE TYPE 1: SUBSTITUTE ANY TEXT IN A GIVEN TEXT
Let us take a sentence ” The moon revolves around the earth”
Let us try to replace the moon and earth with the words Earth and Sun.
STEPS TO SUBSTITUTE WORDS IN THE GIVEN TEXT:
Select the cell where we want the result.
This will be done in two steps.
In first step, we will substitute Moon with Earth.
For this, put the formula as =SUBSTITUTE(F39,”Moon”,”Earth”)
In second step, we will substitute, Earth with Sun.
For this, put the formula as =SUBSTITUTE(F40,”Earth”,”Sun”,2)
The final text will appear as The Earth revolves around the Sun.
EXAMPLE TYPE 2: SEARCHING AND SUBSTITUTING TEXT IN THE GIVEN TEXT
Let us take a sentence ” Humpty Dumpty sat on a wall”
Let us try to find out the word Humpty and replace it with the word Sumpty.
STEPS TO FIND OUT THE POSITION OF THE TEXT PATTERN IN THE GIVEN SENTENCE:
Select the cell where we want the result.
For substituting the word ‘Humpty’ with ‘Sumpty’ , put the formula as =SUBSTITUTE(F47,”Humpty”,”Sumpty”)
The result would appear as Sumpty Dumpty sat on a wall.
The picture below shows the result and other details.
Let us understand the working of the formula.
We used the formula
=SUBSTITUTE(F47,”Humpty”,”Sumpty”)The first argument is the cell address of the TEXT in which we have to substitute the text.The second argument is the WORD WHICH IS TO BE REPLACED.The third argument is the WORD , WHICH WILL BE REPLACING i.e. the NEW WORD.
EXAMPLE TYPE 3: SUBSTITUTE TEXT USING WILDCARD
SUBSTITUTE FUNCTION doesn’t support the WILDCARDS. We need to give the complete text correctly which we want to find and substitute.
Although if we try to use WILDCARD , it won’t result in any error but it won’t solve the problem either.
difference between SUBSTITUTE AND REPLACE FUNCTIONS
SUBSTITUTE and REPLACE functions almost do the same job of replacing the text but in different ways. So, we must be very clear about the use of these functions.
If we know the word to be replaced, SUBSTITUTE FUNCTION is best for us as we just need to input the word to be replaced and the replacement.