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.

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

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

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

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

3. 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

4. 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.