Go Back

Excel REPLACE Function

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

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

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

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

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

 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

replace value on the summary tab. How to use replace value if formula has YTD and Y column that you want to replace
Solved by B. J. in 19 mins
How do you replace the sum of formula with its resulting digits. ie replace =SUM(J21:AC21) with 76
Solved by D. C. in 24 mins
I want to replace entire column............................................
Solved by B. B. in 11 mins

Leave a Comment

avatar