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.
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.
How to Use REPLACE Function in Excel: 3 Suitable Examples
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.
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.
Read More: How to Replace Text in Selected Cells in Excel
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.
Read More: How to Replace Text with Blank Cell 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.
Download Practice Workbook
You can download the Excel workbook that we’ve used to prepare this article.
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.
Excel REPLACE Function: Knowledge Hub
- Excel Formula to Replace Text with Number
- How to Replace Text between Two Characters in Excel
- How to Replace Text after Specific Character in Excel
- How to Replace Text with Carriage Return in Excel
<< Go Back to Excel Functions | Learn Excel
