What is the Excel REPLACE Function?
The excel REPLACE function replaces the text based on a predetermined position.
Excel has another function to change a text, namely the excel SUBSTITUTE function. What’s the difference between the REPLACE function and the SUBSTITUTE function? Please read the following article:
REPLACE Syntax
REPLACE(old_text, start_num, num_chars, new_text)
old_text, required, the text in which you want to replace.
start_num, required, the starting position of the character to replace with the new_text.
num_chars, required, the number of characters to replace with the new_text.
Usage notes:
- If you do not know the position of the character to be changed, use the excel FIND function or excel SEARCH function to find its position.
How to Use REPLACE Function in Excel
The following is an example of the REPLACE function usage and the results.
REPLACE Function #1
=REPLACE(A2,B2,C2,D2)
The REPLACE function replaces 2 characters in position 1 with characters “19”. The result is 19-0001
REPLACE Function #2 – REPLACE Function #4
=REPLACE(A3,B3,C3,D3) =REPLACE(A4,B4,C4,D4) =REPLACE(A5,B5,C5,D5)
All functions return a #VALUE error. You can’t use negative numbers for the start_num and num_chars arguments. In addition to negative numbers, you can’t use zero number in the start_num argument.
REPLACE Function #5
=REPLACE(A6,B6,C6,D6)
If you use zero number for the num_chars argument the result is like adding new_text to old_text; no characters changed, where the new_text will be added depending on the value of the start_num argument.
REPLACE Function #6
=REPLACE(A7,B7,C7,D7)
If the start_num argument is greater than the text length, new_text will be added to the end of old_text. Regardless of the numbers entered in the num_chars argument, it does not affect the results.
