SUBSTITUTE 與 REPLACE：Excel 和 Google Sheets 該用哪一個

Which versions this applies to

The results below were measured in Google Sheets on November 12, 2026. Microsoft documents both SUBSTITUTE and REPLACE for Excel. I did not re-test these strings in desktop Excel today.

SUBSTITUTE changes by value, and can target one instance

B2 was banana:

=SUBSTITUTE(B2,"a","X")      → bXnXnX
=SUBSTITUTE(B2,"a","X",2)    → banXna
=SUBSTITUTE(B2,"A","X")      → banana

With no instance number, every a was replaced. With instance 2, only the second a became X (the first and third stayed). An uppercase "A" left the word unchanged, so the match is case-sensitive.

Removing a character is the same function with an empty replacement. SUBSTITUTE("SKU-103","-","") returned SKU103.

REPLACE changes by position

REPLACE does not look for a letter. It overwrites a slice:

=REPLACE(B2,2,2,"XX")     → bXXana
=REPLACE("SKU-103",4,1,"/")    → SKU/103
=REPLACE("Apple Pie",1,5,"Cake")  → Cake Pie

In banana, position 2 length 2 is the letters an, so they became XX. In SKU-103, the hyphen is the fourth character (S K U - …); REPLACE at position 4, length 1, put a slash there and returned SKU/103. I first tried position 5 by counting wrong and got SKU-/03 — the 1 was replaced, not the hyphen. Count the characters, or you will edit the wrong slice.

REPLACE("Apple Pie",1,5,"Cake") replaced Apple and left Pie, so the result was Cake Pie.

Letter case in SUBSTITUTE

B4 was Apple Pie:

=SUBSTITUTE(B4,"apple","X")    → Apple Pie
=SUBSTITUTE(B4,"Apple","X")    → X Pie

The lowercase search text did not match Apple. The exact-case search did. If you need a case-insensitive replace, I did not test a workaround in this file; I would not invent one. For case-sensitive lookups, see Case-Sensitive Lookup. For case-sensitive search inside a string, FIND vs SEARCH is the closer comparison (FIND is case-sensitive; SEARCH is not).

When to use which

• You want every hyphen gone, or only the second comma changed: SUBSTITUTE.
• You want whatever sits in positions 4 through 5 changed, even if the character varies from row to row: REPLACE.
• You are tempted to use REPLACE to change a hyphen in SKU-103 without checking the position: do not. Position 5 was the digit 1 in this test.

Neither function splits a cell into columns. That is SPLIT / TEXTSPLIT.

Excel and Google Sheets differences

Microsoft documents the same two functions, including SUBSTITUTE’s optional instance number, for Excel. I am not quoting Excel strings from this run.

Common mistakes

• Using REPLACE when you mean “change this letter wherever it is.” REPLACE starts at a position, not at a search string.
• Off-by-one positions. REPLACE("SKU-103",5,1,"/") was SKU-/03, not SKU/103.
• Expecting SUBSTITUTE to ignore case. "A" did not change banana; "apple" did not change Apple Pie.
• Forgetting the instance argument and replacing every match. SUBSTITUTE("banana","a","X") changed all three as.

FAQ

What is the difference between SUBSTITUTE and REPLACE?

SUBSTITUTE finds text and replaces it, optionally only the nth instance. REPLACE overwrites a run of characters at a known position. SUBSTITUTE("banana","a","X",2) was banXna; REPLACE("banana",2,2,"XX") was bXXana.

How do I replace only the second occurrence?

=SUBSTITUTE(text,old,new,2). In banana, that turned only the second a into X.

Is SUBSTITUTE case-sensitive?

Yes, in this test. SUBSTITUTE("banana","A","X") returned banana. SUBSTITUTE("Apple Pie","apple","X") returned Apple Pie.

How do I change a hyphen in a SKU to a slash?

If every SKU has the hyphen in the same place, REPLACE(text,4,1,"/") returned SKU/103 for SKU-103. If the hyphen can move, SUBSTITUTE(text,"-","/") is safer; I did not run that exact slash substitution, but SUBSTITUTE("SKU-103","-","") did remove the hyphen.

Will these functions work in Excel?

Microsoft documents both for Excel. I did not re-run these strings in desktop Excel today.

Related tutorials

• FIND vs SEARCH in Excel and Google Sheets
• Split Text by Delimiter in Excel and Google Sheets
• TRIM Not Working in Excel? Remove Non-Breaking Spaces (CHAR 160)
• Keep Leading Zeros in Excel and Google Sheets
• Case-Sensitive Lookup in Excel and Google Sheets

Live-test findings

Google Sheets, November 12, 2026. SUBSTITUTE("banana","a","X") → bXnXnX; instance 2 → banXna; "A" → banana. REPLACE("banana",2,2,"XX") → bXXana. SUBSTITUTE("SKU-103","-","") → SKU103. REPLACE("SKU-103",5,1,"/") → SKU-/03; position 4 → SKU/103. SUBSTITUTE("Apple Pie","apple","X") unchanged; "Apple" → X Pie. REPLACE("Apple Pie",1,5,"Cake") → Cake Pie.

Sources

• Microsoft Support: SUBSTITUTE function: https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332
• Microsoft Support: REPLACE, REPLACEB functions: https://support.microsoft.com/en-us/office/replace-replaceb-functions-8d799074-2425-4a8a-84bc-82472868878a
• Microsoft Support: EXACT function: https://support.microsoft.com/en-us/office/exact-function-d3087698-fc15-4a15-9631-12575cf29926
• Google Docs Editors Help: SUBSTITUTE: https://support.google.com/docs/answer/3094215
• Google Docs Editors Help: REPLACE: https://support.google.com/docs/answer/3098247