Replacing characters in text with the REPLACE function
The REPLACE function substitutes a text string with another text string.
Let’s say your boss tells you that the product IDs for a product line must be changed.
But only a part of the product ID should be changed – not all of it.
Here, the “29FA” part of all the product IDs needs to be changed to “39LU”.
To do that with the REPLACE function, we’ll walk through the syntax of the REPLACE function, which goes like this:
=REPLACE(old_text, start_num, num_chars, new_text)
Don’t worry, it’s not as daunting as it looks. It’s actually pretty straightforward👍
Step 1: Old text
The old text argument is a reference to the cell where you want to replace some text. Write:
And put a comma to wrap up the first argument, and let’s move on to the next.
Step 2: Start num
The start_num argument determines where the REPLACE function should start replacing characters from.
In our case, the “29FA” part starts on the 3rd character in the text.
Now, we’ve established where the REPLACE function should start to replace text.
Still with me? Then let’s dive into the next argument of the REPLACE syntax🤿
Step 3: Num chars
Also called the “number of characters” argument, this determines how many characters should be replaced with the new text.
Typically, this should be the length of the text you want to replace the old text with.
So, it ties together with the next argument.
If you want to replace “29FA” with “39LU”, then you’re replacing the next 4 characters.
=REPLACE(A2, 3, 4
And wrap it up with a comma🎁
There are situations where this num_chars argument should be a different length than the new_text argument. I’ll tell you more about that later.
Step 4: New text
The new_text argument is the replacement text for the old text.
So, simply write the new characters that should replace the old 4 characters:
=REPLACE(A2, 3, 4, “39LU”
Remember the double quotes when the replacement text is letters or a combination of numbers and letters.
Wrap up the formula with an end parenthesis and press Enter.
Now, the “29FA” part of the old product ID is replaced with “39LU”.