In this article, we will have a look at the REPLACE function. We will look at how to use this function to change existing text in a string with new text. We will also look at how the REPLACE function is different to the SUBSTITUTE function and when to use each one.
REPLACE Function
The REPLACE function requires 4 variable input. Before we start explaining what each field means, it is best to think of the REPLACE function as first removing a portion (or all of the) existing text and then replacing it with new text :
- Old_text: this is the current text/string or cell reference we would like to edit
- Start_num: this is the starting position in the old_text from which current text should be removed. For example if we put 4, then Excel will remove the existing text from the 4th character
- Num_chars: from the start_num position, how many characters should be removed?
- New_text: after removing “num_chars” number of characters starting from the “start_num” position, what should be replaced?
It is important to note that the number of characters in the “new_text” does not need to match the “num_chars”. This means you could remove 3 characters in the current “old_text” and replace it with a “new_text” that is 2 characters or 9 characters.
Let’s go through a simple example:
=REPLACE(A2,1,7,”Team-C-“)
In this case, we have a list of people from Team A and from Team B. And we are merging the two teams to become Team C. For each row, first we are removing 7 characters (num_chars: 7) starting from the first position (start_num: 1) and we are replacing it with “Team-C-” as the new text (“new_text”).
Here’s another example:
=REPLACE(A2,1,8,””)
Here we simply want to remove the first 8 characters so we are left with the name in each cell. In this case, we are removing the first 8 characters and replacing it with…nothing as the new text (“”).
And finally we have a slightly more complex formula where we combine the FIND function with REPLACE function:
=REPLACE(A2,1,FIND(” “,A2),””)
The example is only a little more complex because the number of characters to remove in each row is different. But we do know that the two words are separated by a space. So we want to remove all characters up to the space. This means we just need to find the position of the space which we can do with FIND function: FIND(” “, A2). After that, it becomes a very similar example as the previous one – starting from 1 position, we remove nth number of characters depending on where the space is and we replace it with “” (nothing).
REPLACE Function vs SUBSTITUTE Function
The REPLACE function and the SUBSTITUTE function serve a very similar purpose, if not the same. In fact, if we look at the definitions of each function in Excel, they are hardly distinguishable:
The difference lies in how each one is used:
- REPLACE function removes existing strings with users specifying the starting position and the number of characters to be removed
- SUBSTITUTE function removes existing strings with users specifying specifically what character(s) to remove
That means if you know exactly what you are removing from the original text, you should use the SUBSTITUTE function. If you only know the position from which you want to remove and replace in the original text, you should use the REPLACE function.
Here’s an example to illustrate the difference:
In the example of the left, we want to remove the first 8 characters for every row so the REPLACE function is more suitable. With the second example, we don’t necessarily want to make any changes in every row. We only want to change all the “HFI” with “Anonymous”. And in this case, SUBSTITUTE function is more suitable because all the other strings that do not have “HFI” will remain unchanged.
We hope you find this article useful. If you have any more questions regarding replacing existing texts in Excel, please leave a comment below and we will keep editing this article to make it more and more useful for everyone.
0 Comments Leave a comment