Excel REPLACE Function (Table of Contents)
REPLACE in Excel
REPLACE Function is a pre-built integrated function in excel that is categorized under TEXT function
It is most commonly used to locate specific information & replace string, substring, text characters, numbers in a cell with required data
Usually, when we import or copy a data from any database or another source, it contains unwanted characters, Here the Excel replace function will help out to replace these characters or add with specific or required text
Definition
Excel REPLACE function replaces part of the text in a specified position with a different text or new text string
REPLACE Formula in Excel
Below is the REPLACE Formula in Excel:
The REPLACE function in Excel has the below arguments:
- Old_text (Compulsory or required parameter): It is the cell reference containing the text which you want to replace. (It may contain text or numeric data)
- Start_Num (Compulsory or required parameter): It is the starting position from where the search should begin i.e. From the left side of the character in the old_text argument
- Num_chars (Compulsory or required parameter): It is the number of characters you want to replace.
- new_text (Compulsory or required parameter): It is the new text that you’d like to replace the old_text
Note: new_text argument does not need to be similar or same length as num_chars.
How to use REPLACE Function in Excel?
REPLACE Function is very simple to use. Let us now see how to use REPLACE function in Excel with the help of some examples.
Example #1 – REPLACE Function for Name Change
In the below-mentioned example, Cell “B8”, it contains the name “Andrew Edward”. Here I need to REPLACE that name with the correct name i.e. John Edward with the help of REPLACE function
Let’s apply to REPLACE function in cell “C8”. Select the cell “C8” where REPLACE function needs to be applied.
Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “REPLACE” in the search for a function box, REPLACE function will appear in select a function box. Double click on REPLACE function.
A dialog box appears where arguments for REPLACE function needs to be filled or entered i.e.
=REPLACE(old_text, start_num, num_chars, new_text)
Old_text: is the cell reference containing the text which you want to replace. i.e. “B8” or “Andrew Edward”
Start_Num: It is the starting position from where the search should begin i.e. From the left side of the character in old_text argument i.e. 1
Num_chars or Number_of_chars: It is the number of characters you want to replace. i.e. The word ANDREW contains 6 letters which I need to replace, therefore it is 6
new_text: It is the new text that you’d like to replace the old_text, here “John” is a new string & “Andrew” is the old string. i.e. Here we have to enter new string i.e. “John”
Click ok, after entering all the replace function arguments.
=REPLACE(B8,1,6,”John”)
It replaces text in a specified position of a given or supplied string i.e. John Edward in the cell C8
Example #2 – Addition Of Missing Word In A Text
In the below-mentioned example, In the cell “B12”, it contains the word “News”. Here I need to add the missing word i.e. “Paper”. With the help of REPLACE function.
Let’s apply to REPLACE function in cell “C12”. Select the cell “C12” where REPLACE function needs to be applied,
Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “REPLACE” in the search for a function box, REPLACE function will appear in select a function box. Double click on REPLACE function.
A dialog box appears where arguments for REPLACE function needs to be filled or entered i.e.
=REPLACE(old_text, start_num, num_chars, new_text)
Old_text: is the cell reference containing the text which you want to replace. i.e. “B12” or “News”
Start_Num: From the left side of the character in old_text argument (News) i.e. 5, from 5th position add a New_text
Num_chars or Number_of_chars: Here it is left blank. i.e. We are not replacing anything here, as we are adding a missing word to old_text.
New_text: We are not replacing anything here, here “ Paper” is a new string. Therefore, we have to enter a new string which has to be added to that old text. i.e. “ Paper”
Note: We had to add one space, before the word “paper”
Click ok, after entering all the replace function arguments.
=REPLACE(B12,5,,” Paper”)
It adds new text in a specified position i.e. “News Paper” in the cell C12
Example #3 – Addition Of Hyphen In Phone Number
In the below-mentioned example, Cell “G8” contains contact number with state code “0224038991”. Here I need to add a hyphen after state code with the help of replace function. i.e. “022-4038991”
Let’s apply to REPLACE function in cell “H8”. Select the cell “H8” where REPLACE function needs to be applied.
Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “REPLACE” in the search for a function box, REPLACE function will appear in select a function box. Double click on REPLACE function.
A dialog box appears where arguments for REPLACE function needs to be filled or entered i.e.
=REPLACE(old_text, start_num, num_chars, new_text)
Old_text: is the cell reference containing the text which you want to replace. i.e. “G8” or “0224038991”
Start_Num: From the left side of the character in old_text argument i.e. 4. At 4th position add hyphen “-”
Num_chars or Number_of_chars: Here it is left blank. i.e. We are not replacing anything here, as we are adding Hyphen in between contact number
New_text: We are not replacing anything here, “ – ” i.e. Hyphen is a special character or new string Which has to be added in between an old string
Note: We had to add one space before & after hyphen “ – ” to get the desired result
Click ok, after entering the all the arguments in replace function.
=REPLACE(“0224038991″,4,,” – “)
It replaces hyphen “-” in a specified position i.e. “022-4038991” in the cell C12
Things to Remember About REPLACE Function in Excel
- #VALUE error occurs if start_num or num_chars argument is a non-numeric or negative value.
It will throw Value Error.
- #NAME error Occurs if the Old_text argument is not enclosed in double quotation marks
It will throw Name Error.
Recommended Articles
This has been a guide REPLACE in Excel. Here we discuss the REPLACE Formula and how to use REPLACE function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
