Excel REPLACE Function

Replace Function in Excel

Replace function in excel is a text function which is an inbuilt function and similar to substitute function, this function is used to replace an old text from a string with a new string, the input required by this function is the old text new text and the starting numbers and ending numbers of the characters which needs to be replaced.

Syntax

Replace Formula in Excel

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 a 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.
REPLACE-Function-in-Excel

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For 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.  As a VBA function, it can be used in macro code, which is entered through the Microsoft Visual Basic Editor integrated with MS Excel. Refer to the examples given below to understand better.

You can download this REPLACE Function Excel Template here – REPLACE Function Excel Template

Example #1 – Replace a string

REPLACE Function Example 1

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

Follow the steps to use replace function in excel.

  1. The first argument of the REPLACE function is B4, which contains the original string to be replaced.

  2. Second argument is 1, which indicates the starting letter of the original string.

  3. Third argument is 4, which is a number of characters to be replaced.

  4. Fourth and the last parameter is ‘Stephen,’ which is a new string to be replaced with.

Here, the old string is ‘John,’ and the new string is ‘Stephen.’

Example #2 – Replace a Substring

REPLACE Function Example 2

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.
  • 2ND argument is 5, which indicates the starting letter of the original string.
  • 3rd argument is 5, which is the number of characters to be replaced.
  • 4th and the last parameter is yahoo, which is a new string to be replaced with.

Here, the old string is ‘gmail,’ and the new string is ‘yahoo.’ As a result, C6 is updated with ‘xyz@yahoo.com’

Example #3 – Replace a Single Character

Example 3

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.
  • 2ND argument is 1, which indicates the starting letter of the original string.
  • 3rd argument is 1, which is a number of characters to be replaced.
  • 4th and the 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

Example 4

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.
  • 2ND argument is 7, which indicates the starting letter of the original string.
  • 3rd argument is 4, which is the number of characters to be replaced.
  • 4th and the last parameter is ‘2000’, which is a new string to be replaced with.

Here, the old string is ‘1989’, and the new string is ‘2000’. As a result, C8 is updated with ’23-12-2000’.

Example #5 – Remove a string

Example 5

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.
  • 2ND argument is 1, which indicates the starting letter of the original string.
  • 3rd argument is 11, which is a number of characters to be replaced.
  • 4th, and the 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

example 6

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.
  • 2ND argument is 0.

However, any string in an excel worksheet cell starts with 1, i.e., 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

  • 2ND parameter, i.e., Start, cannot have a non-numeric or a negative value.
  • 3rd parameter, i.e., number_of_chars, cannot have a non-numeric or a negative value.

REPLACE Excel Function Video

 

Recommended Articles

This has been a guide to REPLACE Function in Excel. Here we discuss the REPLACE Formula in excel and how to use it along with excel examples and a downloadable template. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *