How to Replace Characters, Text, and Strings in Excel

Need to replace text in multiple cells?

Excel’s REPLACE and SUBSTITUTE functions make the process much easier.

Let’s take a look at how the two functions work, how they differ, and how you put them to use in a real spreadsheet🔍

If you want to follow along with what I show you, download my workbook here.

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:

=REPLACE(A2

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.

So, write:

=REPLACE(A2, 3,

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.

Write:

=REPLACE(A2, 3, 4

And wrap it up with a comma🎁

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”.

Pretty cool, right?😎

PRO TIP: Length of num_chars vs new_text

If you need the replacement text to be shorter or longer than the text it’s replacing, you can have a different length in the 3rd and 4th argument of REPLACE. Let me give you a few formula examples of that:

If you wanted to replace the “29FA” with “39L” instead, you’d write:

=REPLACE(A2, 3, 4, “39L”)

But the length of the 4th argument would be shorter than the 4 characters defined in the 3rd argument.

On the other hand, if you wanted to replace “FA” with “LLUU”, you’d write this:

=REPLACE(A2, 5, 2, “LLUU”)

Replacing text strings with the SUBSTITUTE function

If the string you want to replace doesn’t always appear in the same place, you’re better off using the SUBSTITUTE function.

The syntax of SUBSTITUTE goes like this:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

This is a little different from the last syntax of the REPLACE function, so be careful not to get them mixed up⚠️

Step 1: Text

The text argument is just a cell reference to the cell where you want to replace text.

Write:

=SUBSTITUTE(A2

And of course, put a comma to go to the next argument.

Step 2: Old text

The SUBSTITUTE function doesn’t replace characters from a fixed position in a cell.

Instead, it cleverly searches for a text string and begins replacing characters from there🔍

So, if you want to replace the “FA” part of the product ID with “LU”, write:

=SUBSTITUTE(A2, “FU”

Step 3: New text

The new_text argument is what the old text should be replaced with.

For this example, that’s “LU”.

=SUBSTITUTE(A2, “FA”, “LU”

Step 4: Instance num

The optional instance num argument decides how many times the text should be replaced.

This is relevant if there is more than one instance of the old text.

The instance_num argument is optional. If you leave it blank, every instance of the old text is replaced by the new text😊

In the following formula example, the first 2 product IDs each have 2 instances of the “FA”. So, the instance num argument determines whether only the first instance of “FA” is replaced with “LU” or both instances of “FA” is replaced with “LU”.

As you can see from the picture below, write 1 if you want only the first instance of “FA” to be replaced.

=SUBSTITUTE(A2, “FA”, “LU”, 1)

Or don’t use the instance num argument if every instance of “FA” should be replaced:

=SUBSTITUTE(A2, “FA”, “LU”)

And that’s how to replace text dynamically based on the location of the text you want to replace.

The difference between REPLACE and SUBSTITUTE

There are a few subtle differences between these two “replace functions”.

Both of them replace one or more characters in a text string with another text string.

The difference lies in how the first string is identified.

REPLACE selects the first string based on the position. So you might replace four characters, starting with the sixth character in the string.

SUBSTITUTE selects based on whether the string matches a predefined search. You might tell Excel to replace any instance of “FA” with “LU” for example.

Other than that, the two functions are identical👬🏻

Replace text using Find and Replace

Another way to replace text is with the ‘Find and Replace‘ feature of Excel.

It’s a way to substitute characters in the original cell instead of having to add additional columns with formulas.

1. Select all the cells that contain the text to replace.

2. From the ‘Home’ tab, click ‘ Find and Select’.

3. From the Find and Replace dialog box (in the replace tab) write the text you want to replace, in the ‘Find what:’ field.

4. Still within the ‘Find and Replace’ dialog box, write the new text to replace the old text with in the ‘Replace with:’ field.

5. When you click the ‘Replace all’ button, Excel replaces all instances of the old text with the new text, in the selected cells.

That’s it – Now what?

With the REPLACE and SUBSTITUTE functions, you can replace very specific strings with other strings. You can use letters, numbers, or other characters.

In short, you can replace text with extreme accuracy. And that saves you a great deal of time when you need to make a lot of edits.

Additionally, you can use the Find and Replace tool, which is the most underrated feature of Excel.

But no one got a job offer just based on their skills to replace characters in a text in Microsoft Excel.

Luckily, there are other areas of Excel that are magnets for job offers🧲

Click here to learn IF, SUMIF, VLOOKUP, and pivot tables (yup, that’s the magnets) for FREE in my 30-minute online Excel course.

Other resources

Replacing text is often used to clean up data so it’s ready for analysis, formulas, pivot tables, etc.

Other ways of cleaning up data are with other important Excel functions like LEFT, RIGHT, MID, and LEN.

Or with one of the two ways of deleting blank rows (one better than the other). Read all about it here.