Table of Contents[Hide][Show]
The REPLACE function is a function in excel that is used to replace characters within a text string with another set of characters. This function is only useful when the location of the text to be replaced is known or can be easily identified. In today's post, we would touch on everything you need to know about the REPLACE function, starting from the syntax and argument and ending with examples on the use of the function. Let's start things off with the syntax and argument.
Syntax & Argument
The Syntax of the REPLACE Function is as follows:
=REPLACE(old_text, start_num, num_chars, new_text)
There are a total of 4 arguments and all are required arguments:
- old text: This argument represents the original text string or cell reference which you seek to replace.
- start_num: This is for specifying the starting position within the old text argument to begin the replacement. Please note that the first character is at position 1.
- num_chars: This represents the number of characters to be replaced starting from the start_num argument.
- new_text: The final argument is the new_text argument which represents the new text or string that will replace the specified characters in the old_text argument.
Usage Notes
Now let's see some of the usage notes for the REPLACE Function:
- If you input 0 as the num_chars argument, the replace function will insert the new text without deleting any character of the old text.
- If the start_num exceeds the length of the old text, then the function will return the original text and the next text right after
- The start_num parameter must be a positive integer that represents the position of the first character to be replaced. In a case where the start_num is less than 1 or exceeds the length of the old text, the REPLACE function would return an error.
- The REPLACE Function is case sensitive. This means that it would only replace characters if the case matches exactly.
REPLACE Function Examples
In this section we would go over some examples to help you better understand the REPLACE function.
Replacing hardcoded values
Here we would replace a text manually written in the function syntax. Our text will be "Royalford" and we seek to replace the text "ford" with the text "board". Our syntax to achieve this is as follows: