Think of a scenario!
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.
- Basic understanding of rows and columns in Excel.
- Some information about the TEXT HANDLING IN EXCEL is an advantage for the use of such formulas.
- Of course, Excel software.
SYNTAX: SUBSTITUTE FUNCTION
The Syntax for the SUBSTITUTE function is
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.
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”)
- Click Enter.
- 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
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
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.
If we know the character position of the word to be replaced, we should use REPLACE FUNCTION . Although with the help of SEARCH and FIND function we can search the location for a given word and make the REPLACE FUNCTION work like the SUBSTITUTE FUNCTION.
OTHER WAYS TO REACH THIS ARTICLE
WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL
YOU MAY LIKE
- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.