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 のまま、"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