How to Use REPLACE Function in Excel (3 Suitable Examples)

In Microsoft Excel, the REPLACE function is generally used to replace or substitute a text. The replacement is based on its starting position and defined number of characters. In this article, you’ll get to learn how you can use this REPLACE function effectively in Excel with appropriate illustrations.

replace function overview in excel

The above screenshot is an overview of the article, representing a few applications of the REPLACE function in Excel. You’ll learn more about the methods along with the other functions to use the REPLACE function precisely in the following sections of this article.


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.


Introduction to the REPLACE Function

replace function syntax

  • Function Objective:

REPLACE function is used to replace a part of a text string with a different text string.

  • Syntax:

=REPLACE(old_text, start_num, num_chars, new_text)

  • Arguments Explanation:
Argument Required/Optional Explanation
old_text Required The text within which a part has to be replaced.
start_num Required The starting number of the character of the part that has to be replaced.
num_chars Required The number of characters that have to be replaced with a new text.
new_text Required The text that has to be added by replacing the old one in the text string.
  • Return Parameter:

The function returns with the new text string within which new and replaced text or word is present.


3 Suitable Examples of REPLACE Function in Excel

1. Basic Use of REPLACE Function in Excel

In Column B, a number of random product IDs are present. In Column C, new IDs have to be formed by replacing “NB” with “DT” from the old IDs. The text “NB” has 2 characters, and the starting position of this text is 1 for all IDs in Column B.

So, the required formula in the output Cell C5 should be:

=REPLACE(B5,1,2,"DT")

After pressing Enter, you’ll get the new ID with the replaced text inside. Now you can use Fill Handle to fill down the rest of the cells in Column C if all texts in Column B are in a similar format.

basic use of replace function in excel


2. Use of REPLACE Function to Replace a Text from Any Position in Excel

Although the SUBSTITUTE function is more applicable to replace a particular text from any position in several text strings with a new one, the REPLACE function can also be used to make your own formula.

In Column B, there are a number of random texts where the word “2020” will be replaced with “2021”. If you notice, you’ll find that the word ‘2020’ is lying in different positions, not in a similar position in the entire column. Now we’ll use the REPLACE function to make our own formula to substitute ‘2020’ with ‘2021’.

The related formula with the combination of REPLACE, FIND and LEN functions in Cell C5 will be:

=REPLACE(B5,FIND(2020,B5),LEN(2020),2021)

Press Enter, autofill the entire column and you’ll find the new results with the updated texts like the screenshot below.

replace function to replace a text from any position in excel

In this combined formula, the FIND function identifies the starting position of the text ‘2020’ which is used as the start_num argument for the REPLACE function. LEN function defines the number of characters lying in the text ‘2020’ for the third argument of the REPLACE function.


3. Using Replace Command from the Find & Select Drop-Down in Excel

There is an alternative to the use of the REPLACE function in Microsoft Excel and that is the Replace command. With this command, you can replace or substitute any text in a string with more ease as you won’t have to type a formula manually in the spreadsheet.

In the following picture, similar texts are present as described in the previous section. But now we’ll use the Find and Replace command to substitute ‘2020’ with ‘2021’.

use of find and replace command in excel

📌 Step 1:

➤ Select all the cells containing the word ‘2020’ in Column B.

➤ Press CTRL+H to open the Find and Replace dialogue box.

use of find and replace command in excel

📌 Step 2:

➤ Input the word ‘2020’ in the Find what criteria.

➤ Type ‘2021’ in the ‘Replace with’ box.

➤ Press Replace All.

use of find and replace command in excel

📌 Step 3:

➤ A message box will appear showing that the execution of the command is complete. Press OK and you’re done.

use of find and replace command in excel

Like the following screenshot, the text ‘2020’ will be replaced with ‘2021’ in all cells in Column B.

use of find and replace command in excel


Difference Between REPLACE and SUBSTITUTE Functions in Excel

SUBSTITUTE function is a better alternative to the REPLACE function. You can use the REPLACE function only when you know the start_num and num_chars arguments in numerical values. But while using the SUBSTITUTE function, you won’t even need to input any start_num or num_char arguments. You have to simply input the cell name, the word or the text to be substituted, and the substitute word. So, the generic formula of this SUBSTITUTE function is:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

In the following picture, you’ll notice the basic difference between the uses of REPLACE and SUBSTITUTE functions in Excel. Both formulas extract similar outputs but the SUBSTITUTE function is way too useful for its simpler and more comprehensive arguments.

difference between replace and substitute functions in excel


💡 Things to Keep in Mind

🔺 REPLACE function is useful only when you have to replace a text which is based on the particular position of that string of text.

🔺 REPLACE function will return a #VALUE error if the start_num and num_chars arguments are in non-numeric form.


Concluding Words

I hope all of the suitable methods mentioned above to use the REPLACE function will now allow you to apply them in your Excel spreadsheets with more productivity. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.


You May Also Like to Explore

How to Use FIND Function in Excel (7 Suitable Examples)

How to use XLOOKUP function in Excel (7 Examples)

How to Use LEN Function in Excel (Formula and VBA Code)

How to Use HLOOKUP Function in Excel (8 Suitable Approaches)

How to Use VLOOKUP Function in Excel (2 Examples)

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo