SUBSTITUTE vs REPLACE in Excel: Which One to Use

You need to change “2026” to “2027” in a column of codes, or turn “US-” into “USA-” everywhere it appears. Two functions do this job, and picking the wrong one is a classic beginner mistake: SUBSTITUTE works on the text itself, REPLACE works on the position. Use the wrong one and either nothing changes or the wrong characters change.

What SUBSTITUTE vs REPLACE does

SUBSTITUTE finds a piece of text and replaces it with new text, everywhere or at one occurrence you choose. REPLACE ignores what the text says and replaces a fixed number of characters starting at a fixed position. The rule is simple: if you know what the text says, use SUBSTITUTE; if you only know where it sits, use REPLACE.

The syntax

=SUBSTITUTE(text, old_text, new_text, [instance_num]) — =REPLACE(old_text, start_num, num_chars, new_text)

  • old_text — for SUBSTITUTE: the text to find. For REPLACE: the original text.
  • new_text — the replacement text.
  • instance_num — SUBSTITUTE only: which occurrence to replace. Leave it out to replace all of them.
  • start_num / num_chars — REPLACE only: where to start and how many characters to replace.

Step-by-step example

Fix these product codes: change the year 2026 to 2027, and change the region prefix US to USA.

  1. Click the cell for the fixed code.
  2. Type =SUBSTITUTE(A2,"2026","2027") — every 2026 in the code becomes 2027.
  3. To change only the first occurrence, add the instance number: =SUBSTITUTE(A2,"-","/",1) changes only the first dash.
  4. For the region prefix, use REPLACE on the position: =REPLACE(A2,1,2,"USA") replaces the first two characters with USA.
  5. Press Enter and copy down. Check one row by eye — SUBSTITUTE changes what it finds, so a code without 2026 stays untouched, which is correct.

Worked example

Original code SUBSTITUTE year REPLACE region
US-2026-0042 US-2027-0042 USA-2026-0042
EU-2026-1187 EU-2027-1187 EUA-2026-1187
KR-2025-0093 KR-2025-0093 KRA-2025-0093

The third row shows the difference clearly: SUBSTITUTE left KR-2025-0093 alone because it contains no 2026, while REPLACE changed its first two characters regardless of what they were. Content-based versus position-based — that is the whole distinction.

Common errors

SUBSTITUTE changed nothing. The old text does not match exactly — check for spaces, case, and hidden characters. SUBSTITUTE is case-sensitive: “us” will not replace “US”.

REPLACE cut the wrong characters. Positions count from 1, and separators count too. In “US-2026”, the year starts at position 4, not 3. Count the dash before typing the number.

Only the first occurrence changed. You passed an instance number. Without it, SUBSTITUTE replaces every occurrence — add the number only when you want exactly one.

The result is text, not a number. Both functions return text. If the replaced value should be a number, wrap the result in VALUE — but only when the whole result is numeric.

Tip

SUBSTITUTE is the workhorse: removing spaces (SUBSTITUTE(A2," ","")), fixing separators, standardizing spellings. REPLACE is the specialist for fixed-width codes where the position never changes. If you are unsure, try SUBSTITUTE first.

Nesting SUBSTITUTE for multiple fixes

One SUBSTITUTE handles one replacement, but they nest: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," Ltd","")," Inc","")," Co","") strips three different company suffixes in a single formula. Each inner SUBSTITUTE feeds its result to the next one, so the chain runs left to right. This is the standard way to clean company names, and it beats three helper columns. Keep the chain short — past four or five levels it becomes hard to read, and at that point a lookup table of replacements with a small helper is the cleaner design.

Practice file

Download the SUBSTITUTE vs REPLACE practice file (.xlsx) — it contains product codes with live SUBSTITUTE and REPLACE formulas, including the instance-number trick and a nested chain.

Google Sheets

Google Sheets supports SUBSTITUTE and REPLACE with the same syntax. Sheets also has REGEXREPLACE, which replaces by pattern instead of exact text — useful when the thing to replace varies, such as any number of spaces.

Example prices and figures are illustrative, not market data.