EXCEL FUNCTION-SUBSTITUTE

Table of Contents

INTRODUCTION

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 a 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,v 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.

For example,

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.

Helpful links for the prerequisites mentioned aboveWhat Excel does? How to use formula in Excel?

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.

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.
SUBSTITUTE WORDS IN TEXT
SUBSTITUTING THE WORDS IN A TEXT IN EXCEL

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.
SUBSTITUTE WORD IN EXCEL
SUBSTITUTE WORD IN THE GIVEN TEXT

EXPLANATION:

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.

KNOWLEDGE BYTES

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.

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.