SUBSTITUTE vs REPLACE in Excel: Clean and Transform Text Data

Data cleaning often comes down to two tasks: replacing a specific word or character wherever it appears, or replacing a chunk of text at a known position. Excel provides a dedicated function for each. SUBSTITUTE matches text by content — you tell it what to look for. REPLACE, by contrast, works by position — you tell it where to start and how many characters to overwrite. Choosing the right one makes your formula shorter, clearer, and far less likely to break as data changes.

What Do SUBSTITUTE and REPLACE Do?

The core decision is simple. SUBSTITUTE asks: "What text do you want to swap?" REPLACE asks: "Which characters, by position, do you want to overwrite?" Consequently, SUBSTITUTE is ideal for cleaning inconsistent data — removing dashes from phone numbers, stripping currency symbols, or standardising spellings. REPLACE, however, shines when text always sits at a fixed position — updating a year code in a product reference, masking digits in an ID, or inserting a prefix.

What Is the Syntax for Each Function?

Each function takes a different set of arguments that reflect its approach to replacement.

Example 1: Remove Unwanted Characters with SUBSTITUTE

Removing a character means substituting it with an empty string. This technique cleans dashes from phone numbers, strips currency symbols, removes spaces from codes, and deletes any other character that should not be there. SUBSTITUTE removes every instance of the character by default.

Example 2: Replace a Specific Word or Phrase

SUBSTITUTE replaces words and phrases just as easily as single characters. It scans the entire string and swaps every matching occurrence. This is useful for standardising terminology, correcting bulk misspellings, or updating outdated labels across a dataset.

Example 3: Replace Only the Nth Occurrence

The optional fourth argument in SUBSTITUTE controls which specific occurrence to replace. For instance, a string like "cat-cat-dog" contains "cat" twice. Passing 1 replaces only the first "cat", and passing 2 replaces only the second. This is particularly useful for fixing the second delimiter in a multi-part code without changing the first.

Example 4: Overwrite a Fixed Position with REPLACE

REPLACE is the right choice when you know the position of the characters to change, regardless of what those characters contain. For example, if a product reference always has a 4-digit year starting at character 5, REPLACE can update that year in one formula — even when the year values differ across rows.

Example 5: Mask Sensitive Data with REPLACE

REPLACE is ideal for masking personal or financial data. You can overwrite middle digits of a credit card, phone number, or ID with asterisks. Furthermore, combining REPLACE with LEN makes the mask dynamic — it adjusts automatically to strings of any length.

Example 6: Nested SUBSTITUTE for Multiple Replacements

SUBSTITUTE replaces only one type of character per call. However, nesting multiple SUBSTITUTE functions inside each other lets you clean several characters in a single formula. Each inner SUBSTITUTE feeds its result to the next one, so all replacements happen in sequence.

How to Nest SUBSTITUTE

The innermost SUBSTITUTE runs first. Its output becomes the text argument of the next SUBSTITUTE. Additionally, you can combine nesting with LET to avoid repeating the cell reference and to make the logic easier to follow.

How to Fix Common Errors

SUBSTITUTE returns the original text unchanged

The most common cause is a case mismatch. SUBSTITUTE is case-sensitive, so "pending" does not match "Pending". Check the exact capitalisation of the text in the cell. Alternatively, normalise first with UPPER or LOWER, then apply SUBSTITUTE to the standardised version.

REPLACE returns wrong characters

Either start_num or num_chars is off by one. To verify, use MID to inspect exactly what sits at that position: =MID(A2, start_num, num_chars). If MID returns the correct segment, REPLACE will overwrite it correctly. Adjust the numbers until MID shows the right characters.

Formula returns a text string instead of a number

SUBSTITUTE and REPLACE always return text, even when the result looks like a number. Therefore, if you need to use the result in arithmetic, wrap the formula in VALUE: =VALUE(SUBSTITUTE(A2,"$","")). Alternatively, multiply the result by 1 to trigger an automatic conversion.

Frequently Asked Questions

• What is the difference between SUBSTITUTE and REPLACE in Excel?+
  SUBSTITUTE replaces text by matching its content — you specify what to look for. REPLACE, by contrast, replaces text by position — you specify where to start and how many characters to overwrite. Use SUBSTITUTE when you know the exact text to remove or swap. Use REPLACE when you know the position of the characters to change, regardless of what those characters contain.
• How do I delete a character using SUBSTITUTE?+
  Use an empty string "" as the new_text argument: =SUBSTITUTE(A2, "-", ""). This replaces every hyphen with nothing, effectively deleting it. By default, all occurrences are removed. To remove only the first occurrence, add 1 as the fourth argument: =SUBSTITUTE(A2, "-", "", 1).
• How do I replace only the second occurrence with SUBSTITUTE?+
  Use the fourth argument, instance_num. For example, =SUBSTITUTE(A2, "-", "_", 2) replaces only the second hyphen and leaves all others unchanged. Pass 1 for the first occurrence, 2 for the second, and so on. When the fourth argument is omitted, all occurrences are replaced.

More Questions About SUBSTITUTE and REPLACE

• Is SUBSTITUTE case-sensitive?+
  Yes, SUBSTITUTE is case-sensitive. It treats "Apple", "apple", and "APPLE" as three different strings. Consequently, a formula looking for "Pending" will not match "pending". To work around this, apply UPPER or LOWER to the source text first, then run SUBSTITUTE on the normalised version. REPLACE, by contrast, is not affected by case because it works by position.
• How do I insert text at a specific position using REPLACE?+
  Set num_chars to 0. This tells REPLACE to insert the new text at the specified start position without removing any existing characters. For example, =REPLACE(A2, 1, 0, "NEW-") inserts "NEW-" at the very beginning of the string in A2 without changing the rest.
• Which Excel versions support SUBSTITUTE and REPLACE?+
  Both functions are available in every Excel version from Excel 2000 onwards, including Microsoft 365, Excel 2024, 2021, 2019, 2016, 2013, 2010, and 2007. They also work in Excel for the web and Google Sheets. No special version or add-in is required.