REPLACE Function in Excel – Replace Text String

REPLACE Function is a vital Text Function of Excel. The function is straightforward and quick to understand. REPLACE function’s usage is irreplaceable in many situations when organizing or cleaning the data.

Let’s understand the concept.

When to Use REPLACE Function in Excel?

As the name says, the REPLACE Function in Excel replaces a specific set of characters in a text string with a new text string. We need to specify the starting position and length of characters to replace the old text string.

The REPLACE Function came into existence in 2003 and can be used in later versions of Excel.

Syntax and Arguments

=REPLACE(old_text,start_num,num_chars,new_text)

The following notes have information regarding the inputs required by the REPLACE Function of Excel

• old_text – This is the text string that we want to edit and add a new set of characters to it. It can be a text string in double quotes or a cell reference containing a text string
• start_num – This is the location of the character in the old text string, starting from which, we want to replace the characters.
• num_chars – This is the number of characters from the location specified by start_num that we are going to replace.
• new_text – This is the new text string that we want to replace with the set of characters in the old text string.

Always remember that a text string is a combination of characters. where characters can be alphabets, numbers or special characters. The result of REPLACE Function is formatted as text.

Important Points to Remember about REPLACE Formula

The following points about the REPLACE Function must be kept in mind before you start implementing the formula.

• The REPLACE Function returns an error if any of the four arguments are omitted by the user.
• We cannot supply a negative value, a fractional value or a zero as the start_num or num_chars argument. Otherwise, we would encounter a #VALUE! error as the formula result.
• If we want to remove a set of characters from any text string, then we can replace those characters with an empty text string ( “” ) in double quotes.

Examples to Execute REPLACE Function

So here comes the implementation of REPLACE function. We would start with a basic example.

Example 1 – REPLACE Function with Direct Text Strings as Input

Let us suppose, that I want to replace “le” with “al” in the word “Principle”. Use the following excel formula.

=REPLACE("Principle",8,2,"al")

So you see, that the REPLACE formula’s result is “Principal”.

Explanation – The old text string is “Principle” which is supplied as the first input old_text of the REPLACE Function’s argument. We wanted to replace the last two characters of “Principle” with “al”

When we count from the left of “Principle”, the 8th location is for “le”. This becomes the start_num argument. Since the length of characters to replace is 2, the num_chars argument becomes 2.

The fourth argument is the new_text that we want to add to the old text string. In this case, we are going to replace “le” with “al”. So the fourth argument is “al”.

As a result, the function removes the 2 characters “le” from the 8th location of old_text and adds new_text “al” in its place to give the results.

Example 2 – Editing Product Codes using the REPLACE function

Let us suppose we have the following product codes of a manufacturing company.

The code is in the format CCCC-YYYY-ccc. The fifth character of the code tells the year of the product manufacturing date. We want to replace it with 2019. Use the following Excel Formula in cell B2 for this action.

=REPLACE(A2,5,4,2019)

Select the range B2:B15 and press Ctrl D key to copy the formula down.

As a result, the formula returns the updated product codes.

Explanation – We have supplied cell A2 as the old_text because it contains the old product code. Thereafter, the starting location of the year in the string A2 is 5th and is four characters long. Therefore the start_num and num_chars arguments are supplied as 5 and 4 respectively. Lastly, the new year value is 2019, we can either supply it within the double quotes or without the double quotes because the result is going to be in text format anyways.

As a result, the REPLACE function replaces the year in the product code with 2019.

This brings us to the end of REPLACE Function blog.

Thank you for reading.