An Overview of REPLACE Function | Microsoft Excel
The REPLACE function is one of the text functions in Microsoft Excel. You can use it as a standalone function as well as in collaboration with the other functions. This article will give you an overview of the REPLACE function in Excel.
Objectives
The REPLACE function replaces a string or part of a string with another string. It replaces characters specified by location.
Syntax
=REPLACE(old_text, start_num, num_chars, new_text)Arguments
- old_text: Compulsory. It refers to the string in which you want to replace some characters.
- start_num: Compulsory. It refers to a position in old_text. Starting from this position, the characters will be replaced.
- num_chars: Compulsory. It refers to the number of characters in old_text that you want to replace.
- new_text: It refers to the string that will replace old_text or a part of old_text.
Output
Returns an alternated version of old_text.
Examples of REPLACE Function
Suppose, you have a string “John Wick” in cell A2. Now you want to REPLACE Wick with another string “Doe” in cell B2. To do that you can use the REPLACE function. The formula for this will be :
=REPLACE(A2,6,4,B2)The output will be John Doe.
Here are some examples of the usage of the REPLACE function.
Usage Guide of REPLACE Function
Step_1: Start with inserting an equal sign (=) in a blank cell.
Step_2: Then type REPLACE.
Step_3: Type open parenthesis “(“.
As you type the open parenthesis, Excel shows the syntax of the REPLACE function.
Step_4: Insert a string first. This will be the value of old_text.
You can directly insert a string into the function. If you want the string to be abcd12324, you have to type abcd1234 inside a pair of double inverted commas like “abcd1234”.
You can also insert a cell address that contains a string.
Here, I’m inserting A2 which has the string abcd1234.
Step_5: Then insert a comma (,).
After inserting the comma, Excel will ask you for the second argument which is the start_num.
Step_6: Now insert a number.
You can also insert a cell address that contains a number.
Here, I’m inserting B2 which has the number 5.
This number refers to a position in the old_text. Starting from this position, characters will be replaced.
Step_7: Now insert a comma again.
This time Excel will ask you for the third argument which is num_chars. This argument specifies the number of characters you want to replace.
Here, I want to replace 4 characters. Thus, I’m inserting C2 which has the number 4.
Step_9: Now insert a comma again.
Excel will ask you for the fourth argument which is new_text. This refers to the string that will replace the characters in old_text.
Here, I want efg to replace the 4 characters of old_text. Thus, I’m inserting D2 which has the string efg.
Step_10: After that, insert the closing parenthesis “)”.
Step_11: Finally, hit the ENTER button to insert the formula inside the cell.
Final Result >
The REPLACE function returns abcdefg. Here, the value of old_text is abcd1234. The value of start_num and num_chars are 5 and 4 respectively. The function replaced 4 characters starting from the 5th character. In other words, the function replaced 1234. These 4 characters were replaced with the value of new_text which is efg.
Availability
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Remarks
- The REPLACE function shows #VALUE! error when the start_num value is 0.
- It shows #VALUE! error also when the value of start_num or num_chars is a negative number.
- It erases the specified characters when the value of new_text is empty.
- The REPLACE function treats DATES as 5-digit serial numbers. So, the function replaces characters from the serial numbers of DATES.
- The REPLACE function is not case-sensitive.
Conclusion
The REPLACE function is one of the text functions in Excel. If you have any questions regarding the REPLACE function, please comment below. Thanks!
