SUBSTITUTE 与 REPLACE：Excel 和 Google Sheets 该用哪一个

快速解答

SUBSTITUTE 按文本替换，并可指定第几次；REPLACE 按位置和长度覆盖。本次 SUBSTITUTE("banana","a","X") 得到 bXnXnX，指定第 2 次得到 banXna。REPLACE("banana",2,2,"XX") 得到 bXXana，从第 2 个字符起覆盖 2 个字符。

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

检查要点

SUBSTITUTE 区分大小写：用 "A" 替换 banana 仍是 banana，用 "apple" 替换 Apple Pie 不变，用 "Apple" 得到 X Pie。REPLACE("SKU-103",4,1,"/") 得到 SKU/103；误把连字符当成第 5 位时得到 SKU-/03。

边界与兼容性

SUBSTITUTE("SKU-103","-","") 得到 SKU103。REPLACE("Apple Pie",1,5,"Cake") 得到 Cake Pie。已知要改的文字时用 SUBSTITUTE，只知道位置时用 REPLACE。

完整英文示例、公式与作者备注

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.

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