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 REPLACE FUNCTION which is very simple yet powerful to perform this cumbersome task in fraction of seconds.
As REPLACE FUNCTION is concerned with the text, it is present under the TEXT FUNCTION CATEGORY of the functions.
REPLACE FUNCTION SIMPLY REPLACES ANY PORTION OF A TEXT STRING SPECIFIED BY THE STARTING CHARACTER NUMBER AND THE NUMBER OF CHARACTERS [TEXT SPECIFIED BY THE CHARACTERS] WHICH ARE TO BE REPLACED WITH A NEW TEXT.
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. We need the starting position of the TEXT TO BE REPLACED and the number of characters in that text. For example, in this text the starting character is 12 and the number of charcters is 3. It is somewhat like the MID function when dealing with characters.
In this article we would learn about the purpose, syntax, formula of the REPLACE FUNCTION and get a better understanding with the help of the examples.
PURPOSE OF REPLACE FUNCTION IN EXCEL
REPLACE FUNCTION is used to replace a portion of a text string, which we specify by the position of the character with a new text string.
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 replace this name.
When we have to handle big data like hundreds of names, we can make use of this function.
PREREQUISITES TO LEARN REPLACE 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 above
What Excel does? How to use formula in Excel?
SYNTAX: REPLACE FUNCTION
The Syntax for the REPLACE FUNCTION is
REPLACE (TEXT , START POSITION OF TEXT TO BE CHANGED, NUMBER OF CHARACTERS TO BE CHANGED , NEW TEXT )
- TEXT The text in which we want to perform the substitution.
- START POSITION OF TEXT TO BE CHANGED The position of the first character of the text which we want to replace
- NUMBER OF CHARACTERS TO BE CHANGED The number of characters which we want to replace.
- NEW TEXT This is the new text which will be replacing the old text.
All these options will become more clear through the examples which we’ll discuss later in this article.
EXAMPLES : REPLACE FUNCTION IN EXCEL
EXAMPLE TYPE 1: SUBSTITUTE ANY TEXT IN A GIVEN TEXT
Let us take a sentence ” The sky is clear”
Let us try to replace ‘sky’ with ‘way’
STEPS TO REPLACE WORDS IN THE GIVEN TEXT:
- Select the cell where we want the result.
- For this, put the formula as =REPLACE(E66,5,3,”way”)
- The result will appear as ‘The way is clear’.
- The procedure and formula is shown in the picture below.
The formula used to sort out the problem is
Let us examine this formula step by step.
The first argument is the cell address which contains the text string.
The second argument is the starting character position of the text to be replaced.
The third argument is the number of letters which we want to replace.
WAY has three letters so we put 3 here.The fourth and last argument is the new text which will take place of the old one.
EXAMPLE TYPE 2: SEARCHING AND REPLACING 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.
In this problem, we need to find the position of the Humpty first which we can do with the use of SEARCH or FIND functions.
After searching, the input will go to the REPLACE FUNCTION.
STEPS TO FIND OUT THE POSITION OF THE TEXT AND REPLACE IT IN THE GIVEN SENTENCE:
- Select the cell where we want the result.
- For searching the text and replacing it we use the formula as =REPLACE(E75,SEARCH(“Humpty”,E75,1),6,”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. The formula used is
The first argument is the cell containing the text which is E75.
The second arguments of the REPLACE is the starting position of the text to be replaced.
We can find it using the SEARCH FUNCTION and giving a search to the word to be changed. This process is given
by a nested formula as SEARCH(“Humpty”,E75,1)
It’ll return the starting position of the word Humpty.
Third argument is the number of digits to be replaced, as we want to replace Humpty , which has 6 letters.
And last is the new replacing word.
EXAMPLE TYPE 3: REPLACE TEXT USING WILDCARD
REPLACE FUNCTION doesn’t support the WILDCARDS. As we give the letters to replace manually, there are no chances for the wildcards to work.
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.
If we know the character position of the word to be replaced, we should use REPLACEMENT 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.