Excel REPLACE Function

Excel REPLACE Function (Table of Contents)

• REPLACE in Excel
• REPLACE Formula in Excel
• How to Use REPLACE Function in Excel?

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.

You can download the REPLACE Function Excel Template here – REPLACE Function Excel Template

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 –

1. How to use DAY Excel Function?
2. Guide to AVERAGE Function in Excel
3. Use of Excel COUNTIF Function
4. Examples of YEAR Function in Excel