How to Replace Text by Using the Excel Replace Function

The Excel REPLACE function is helpful when you want to replace a substring of text in a cell with another string of text. The REPLACE function takes four required parameters:

old_text: This is the string of text or, more often, a cell reference containing the text with characters you want to replace.

start_num: This is the starting position in old_text of the characters you want to replace.

num_chars: This is the number of characters in old_text you want to replace.

new_text: This is the new text that replaces the existing text.

Excel REPLACE Function Example

Let’s look at a scenario where you may want to use the REPLACE function. In the spreadsheet example below, we have a list of five product codes in column A.

Excel REPLACE function - magoosh

Suppose we want to create new product codes from the old product codes—such that the middle section, beginning with the letter “H,” gets replaced with a new code, “M25.” In other words, if the old product code is D100-H50-Z9, the new product code should be D100-M25-Z9.

We can do this using Excel’s REPLACE function in column B. In cell B2, we would use the following REPLACE function:

=REPLACE(A2,6,3,”M25″)

This tells Excel to use the value in cell A2 (“D100-H50-79”), count over to the 6th character (“H”) and include 3 characters (“H50”). This text will be replaced with “M25.”

The result looks like this:

Excel REPLACE function - magoosh

If we repeat the process for cells B3 through B6, the result looks like this:

Excel REPLACE function - magoosh

Check out our Excel Lesson Videos to learn how to use more text functions in Excel.

Different Than Search and Replace

You may be wondering why you don’t just use Excel’s built-in search and replace to accomplish the same thing. The difference is that search and replace requires an exact string of characters to replace.

If we had used search and replace, we would have needed to use it three separate times. Once to replace “H50” with “M25,” a second time to replace “H51” with “M25,” and a third time to replace “H38” with “M25.”

And think about it. If we had hundreds of combinations to replace, this would take a long time to do manually. With the REPLACE function, we can perform all the replacements with one common function.

Obviously, using the Excel replace function is less time consuming and irritating! So give it a try, and feel free to leave any comments below.

Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!