The Excel REPLACE function is useful when you want to replace text based on its location in a string. The REPLACE function is able to change some sets of characters within a string of values using the location of the characters in the string.

**Formula**

`=REPLACE(old_text,start_num,num_chars,new_text)`

**How to use Excel REPLACE function**

The Excel REPLACE function is simple to use, as long as you can identify the parameters for the formula arguments.

- old_text: This represents the original string, which is denoted by its cell number e.g. A2
- start_num: This represents the position from which the string will be replaced.
- num_chars: This represents the number of characters that will be replaced by the Excel REPLACE function.
- new_text: This represents the new substring that will replace the old substring in the whole text.

**Example**

Let us assume that for some reason, we need to edit the identification numbers of all the students of a university to match the current academic year.

The following steps should be taken.

- Paste the identification numbers e.g. 11320-2016-ABH

* Figure 1 – List of identification numbers with substrings to be replaced*

- Slot in the Excel REPLACE function formula: =REPLACE(A2,7,4,2018)

*Figure 2 – Showing the excel REPLACE function formula in the cell*

- Drag the extend icon to create formulas for the rest of the identification numbers

*Figure 3 – Showing how to extend the Excel REPLACE formula to the rest of the cells*

- The Result

*Figure 4 – Showing the result of the excel REPLACE function*

**Note**

When replacing based on content rather than location, Substitute function should be used.

## Leave a Comment