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.

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.

**Table of Contents**hide

**Download Practice Workbook**

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

**Introduction to the REPLACE Function**

**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.

**Read More:** **How to Use TEXT Function in Excel (10 Examples)**

**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.

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.

**Similar Readings**

**How to Use CODE Function in Excel (5 Examples)****Use Excel EXACT Function (6 Suitable Examples)****How to Use FIXED Function in Excel (6 Suitable Examples)****Use CLEAN Function in Excel (10 Examples)****How to use TRIM function in Excel (7 Examples)**

**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’**.

**📌**** Step 1:**

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

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

**📌**** Step 2:**

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

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

➤ Press **Replace All**.

**📌**** Step 3:**

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

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

**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.

**💡**** 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 UPPER Function in Excel (4 Examples)****Use SEARCH Function in Excel (3 Examples)****How to use PROPER function in Excel (2 Examples)****Use RIGHT Function in Excel (With 6 Easy Examples)****How to Use LOWER Function in Excel (6 Easy Examples)****Use CONCATENATE Function in Excel (4 Examples)****How to Use REPT Function in Excel (8 Suitable Examples)**