Replace Function in Excel

For example, suppose we have an Excel spreadsheet with numbers and text. We need to replace the number “517” with the other number “987” while retaining the text “ABC.”Using the Excel REPLACE function, we can apply the formula below:

=REPLACE(“ABC517″,4,3,”987”) returns “ABC987”

Syntax

Where,

  • Old_text = This is a required parameter. It is the original string to be replaced.Start = This is the starting position in the original string from where the replacement should begin.Number_of_chars = This is a numeric value and indicates the number of characters to be replaced.New_text= This is another required parameter and indicates the new string/set of characters to be replaced the old_text with.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Excel REPLACE Function (wallstreetmojo.com)

How to use REPLACE Function in Excel? (with Examples)

As a worksheet function, it can be written as a part of a formula in a worksheet cell. It can be used in macro code as a VBA functionVBA FunctionVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more, entered through the Microsoft Visual Basic Editor integrated with MS Excel. You may refer to the examples given below to understand better.

Example #1 – Replace a string

In this example, cell C4 has a REPLACE formula associated with it. So, C4 is a result cell.

The old string is”John,” and the new string is “Stephen.”

  • The first argument of the REPLACE function is B4, which contains the original string to be replaced. The second argument is 1, which indicates the starting letter of the original string. The third argument is 4, which is the number of characters to be replaced. The fourth and last parameter is “Stephen,” a new string to be replaced with.

Example #2 – Replace a Substring

In this example, cell C6 has a formula associated with it. So, C6 is a result cell.

  • The first argument of the REPLACE function is B6, which contains the original string to be replaced.The second argument is 5, which indicates the starting letter of the original string.The third argument is 5, which is the number of characters to be replaced.The fourth and last parameter is yahoo, a new string to be replaced with.

The old string is “gmail,” and the new string is “yahoo.” As a result, C6 is updated with “[email protected].”

Example #3 – Replace a Single Character

In this example, cell C8 has a REPLACE formula associated with it. So, C8 is a result cell.

  • The first argument of the REPLACE function is B8, which contains the original string to be replaced.The second argument is 1, which indicates the starting letter of the original string.The third argument is 1, which is the number of characters to be replaced.The fourth and last parameter is “s,” which is a new character to be replaced with.

Here, the old character is n, and the new character is s. As a result, C8 is updated with “set.”

Example #4 – Replace numbers

In this example, cell C10 has a REPLACE formula associated with it. So, C10 is a result cell.

  • The first argument of the REPLACE function is B10, which contains the original string to be replaced.The second argument is 7, which indicates the starting letter of the original string.The third argument is 4, which is the number of characters to be replaced.The fourth and last parameter is “2000,” a new string to be returned with.

The old string is “1989.” The new string is “2000.” As a result, C8 is updated with “23-12-2000.”

Example #5 – Remove a string

In this example, cell C12 has a REPLACE formula associated with it. So, C12 is a result cell.

  • The first argument of the REPLACE function is B12, which contains the original string to be replaced.The second argument is 1, which indicates the starting letter of the original string.The third argument is 11, which is the number of characters to be replaced.The fourth and last parameter is “” which is a new string (an empty string) to be replaced with.

Here, the old string is “Remove this,” and the new string is “”. As a result, C12 is updated to a blank cell as all the characters are replaced with blanks.

Example #6 – Common Problem with REPLACE Function

In this example, cell C14 has a REPLACE formula associated with it. So, C14 is a result cell.

  • The first argument of the REPLACE function is B14, which contains the original string to be replaced.The second argument is 0.

However, any string in an Excel worksheet cell starts with 1, index 1. So, the result in cell C14 is an error which is #VALUE! Indicating that there is an error in the value.

Things to Remember

  • The second parameter, start, cannot have a non-numeric or a negative value.The third parameter, number_of_chars, cannot have a non-numeric or a negative value.

REPLACE Excel Function Video

This article has been a guide to REPLACE Function in Excel. Here, we discuss the REPLACE formula in Excel and how to use it, Excel examples, and a downloadable template. You may also look at these useful functions in Excel: –

  • Excel VBA Find and ReplaceVBA Replace StringSubstring Function in VBALeft Function in VBA | ExamplesExcel MAXIFS