スプレッドシートで文字をまとめて置換したいのに、関数の書き方やREPLACEとの違いで迷っていませんか。SUBSTITUTE関数は、セルの元データを残したまま文字列だけを安全に差し替えられるのが強みです。この記事では、基本構文、実務で多い7つの置換例、置換できない時の対処法、REGEXREPLACEやGASとの使い分けまでを順番に分かりやすく解説します。
【結論】SUBSTITUTE関数の構文とコピペで使える基本の書き方
結論から言うと、SUBSTITUTE関数は『文字列の中から指定した文字を探し、別の文字に置き換える関数』です。
基本構文は=SUBSTITUTE(文字列, 検索文字列, 置換文字列, [出現回数])で、4つ目は省略できます。
第4引数を省略すると一致した箇所をすべて置換し、指定するとその回数目だけを置換できます。Google ドキュメント エディタ ヘルプ
30秒で分かる基本構文と使用例
最速で使うなら、A2に元の文字列、B2に置換前、C2に置換後を入れ、D2に=SUBSTITUTE(A2,B2,C2)と入力すれば動きます。
たとえばA2が東京本社、B2が東京、C2が大阪なら、結果は大阪本社になります。
この形なら検索文字列や置換文字列をセル参照で管理できるため、担当者名や商品名の差し替えでも流用しやすいです。Google ドキュメント エディタ ヘルプ
元データを残せるオートフィルで数百行に展開しやすい置換ルールをセルで見える化できる
動画で手順を確認したい場合は、こちらの解説動画も参考になります。
各引数の役割と「置換対象」省略時の挙動
第1引数は文字列、第2引数は探す文字、第3引数は置き換える文字、第4引数は何番目の一致だけを置換するかを表します。
実務では第1から第3引数までが必須で、第4引数だけが任意です。
つまり、=SUBSTITUTE(A2,B2,C2)は全件置換、=SUBSTITUTE(A2,B2,C2,2)は2回目だけ置換、という理解で十分です。Google ドキュメント エディタ ヘルプ
引数の意味を曖昧にすると、意図せずすべてを書き換えてしまうので、部分置換が必要な時だけ第4引数を追加すると覚えると失敗しにくくなります。SUBSTITUTE関数とは？Googleスプレッドシートで文字列を …
SUBSTITUTE関数を使い分けるための基礎知識
SUBSTITUTE関数は便利ですが、どの場面でも最適とは限りません。
文字列で探すのか、位置で切り替えるのか、元データを残したいのかで選ぶ関数や機能が変わります。
ここを理解しておくと、作業スピードが大きく変わります。
REPLACE関数との違い｜判断基準を解説
判断基準は単純で、文字そのものを探して置換するならSUBSTITUTE、何文字目から何文字分を置き換えるならREPLACEです。
たとえば東京支店の東京を大阪にするならSUBSTITUTEが自然で、社員コードの先頭3文字だけを差し替えるならREPLACEの方が向いています。
比較軸SUBSTITUTEREPLACE基準一致した文字列開始位置と文字数向く作業単語差し替え桁数固定のコード編集例会社名変更先頭4桁の修正
位置が毎回変わる文字列ならREPLACEはずれやすく、検索文字列が毎回同じならSUBSTITUTEの方が安全です。REPLACEの考え方は、REPLACE解説動画と見比べると理解しやすいです。
「検索と置換」機能との使い分け
一度だけ表全体を直すなら『検索と置換』機能、今後も自動で置換結果を表示したいならSUBSTITUTE関数が適しています。
検索と置換は元セルを書き換える一方で、SUBSTITUTEは別セルに結果を出せるため、変換前と変換後を並べて確認できます。
顧客名簿や商品マスタのように、監査や確認が必要なデータではこの違いが大きいです。Googleスプレッドシートで特定の文字列を自動で置換する3 …
単発の手直しは検索と置換毎回自動で整形したいならSUBSTITUTE元データを残す必要があるなら関数を優先
実務で即使えるSUBSTITUTE置換パターン7選
ここからは、電話番号、氏名、住所、商品説明などでよく出る実務パターンを7つに絞って紹介します。
すべてセル参照か文字コードで書ける形にしているので、そのまま応用しやすい構成です。
ハイフン・ダッシュを削除する
電話番号や郵便番号の整形では、半角ハイフンと全角ハイフンが混在することが多く、1種類だけ消すと取りこぼしが出ます。
まず試したい式は=SUBSTITUTE(SUBSTITUTE(A2,CHAR(45),T(N())),UNICHAR(65293),T(N()))です。
これで123-4567と123－4567の両方に対応しやすくなり、顧客データの表記ゆれを抑えられます。
スペース（空白）を削除する
氏名や住所の整形では、半角スペースだけでなく全角スペースも一緒に消すのが基本です。
実務向けの式は=SUBSTITUTE(SUBSTITUTE(A2,CHAR(32),T(N())),UNICHAR(12288),T(N()))です。
この式なら山田 太郎と山田 太郎の両方を山田太郎へ寄せられるため、照合や重複チェックの精度が上がります。
空白削除だけを動画で確認したい場合は、こちらの動画が分かりやすいです。
改行を削除する
フォーム入力やコピー貼り付け後のセルでは、見えない改行が混ざって並べ替えや検索を邪魔することがあります。
その場合は=SUBSTITUTE(SUBSTITUTE(A2,CHAR(10),T(N())),CHAR(13),T(N()))で改行コードを取り除けます。
CHAR(10)は改行、CHAR(13)は環境によって残る復帰コードなので、両方を消すと安定します。
複数の文字を一括で置換する（ネスト構文）
SUBSTITUTEは1回で1種類の置換ですが、関数を入れ子にすると複数ルールを連続処理できます。
基本形は=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,B2,C2),D2,E2),F2,G2)で、3組の置換ルールを順番に適用します。
住所の株式会社を㈱に変える、スペースを消す、ハイフンを統一する、といった処理を1セルでまとめられるのが利点です。（Gスプレッドシート）複数条件の置き換えを一括して行う方法
ルールが4個以上に増えると式が長くなるため、置換表を別シートに置いて管理するとメンテナンスしやすくなります。
N番目の文字だけを置換する（第4引数の活用）
特定の出現回数だけ変えたい時は、第4引数を使います。
たとえばA2の文字列に同じ記号が3回あり、2回目だけ置換するなら=SUBSTITUTE(A2,B2,C2,2)です。
商品コードの2つ目の区切り記号だけ変える、文章中の2回目だけ注釈記号へ変える、といった用途で役立ちます。Google ドキュメント エディタ ヘルプ
全角・半角を変換する
SUBSTITUTEでできるのは、全角半角の完全自動変換ではなく、必要な文字だけを個別に置き換える方法です。
よくある記号なら=SUBSTITUTE(SUBSTITUTE(A2,UNICHAR(65288),CHAR(40)),UNICHAR(65289),CHAR(41))のように、全角かっこを半角かっこへ寄せられます。
見積書番号や型番で使う記号が限られているなら十分実用的ですが、英数字全体を一括変換したい場面には向きません。
特定の単語を記号で囲んで強調する
文字を消すだけでなく、特定の単語を囲んで見やすくする加工にもSUBSTITUTEは使えます。
B2に強調したい単語、D2に【、E2に】を入れれば、=SUBSTITUTE(A2,B2,D2&B2&E2)でその単語だけを囲めます。
議事録で要確認を【要確認】へ変える、商品名だけを目立たせる、といった注記作業を関数で自動化できます。【Google スプレッドシート】SUBSTITUTE関数を利用して、「,」 …
SUBSTITUTEで置換できない時の原因と対処法
置換できない時は、関数が壊れているのではなく、文字の種類や一致条件が想定とずれているケースが大半です。
特に空白、改行、全角半角、大小文字の4つは見落としやすく、実務でもつまずきやすいポイントです。
置換されない原因チェックリスト5項目
最初に確認したいのは5項目です。
検索文字列が全角と半角でずれていないか半角スペースと全角スペースが混在していないか改行コードが入っていないか英字の大文字と小文字が違っていないか第4引数で対象回数を限定し過ぎていないか
この5つを見直すだけで、多くのエラーは解消できます。
SUBSTITUTEは一致した文字列だけを置換するため、1文字でも違うと結果が変わりません。Google ドキュメント エディタ ヘルプ
大文字・小文字を区別せずに置換する方法
SUBSTITUTEは基本的に大文字と小文字を区別して一致判定するため、ABCとabcは別物として扱われます。
簡易対応なら、=SUBSTITUTE(LOWER(A2),LOWER(B2),C2)のように両方を小文字へそろえてから置換します。
ただしこの方法は、置換対象以外の元文字列が小文字になるため、元の大文字・小文字の表記は保ちにくいです。
見えない文字（空白・改行）を特定して削除する
見えない文字を見つける時は、いきなり削除する前に可視化すると原因を切り分けやすくなります。
たとえばB2に[改行]、C2に[空白]と入れ、=SUBSTITUTE(SUBSTITUTE(A2,CHAR(10),B2),CHAR(32),C2)とすると、どこに混入しているか目で確認できます。
場所が分かったら、同じ式の置換文字列をT(N())へ変えるだけで削除できます。
文字数の差を見たい時は=LEN(A2)と置換後の長さを比較すると、不要文字の有無を数値で確認できます。
SUBSTITUTEの限界と代替手段
SUBSTITUTEは単純な文字列置換に強い一方で、表記ゆれが多いケースや大量処理では限界があります。
ルールの複雑さとデータ量に応じて、REGEXREPLACEやGASへ切り替えるのが効率的です。
パターンマッチが必要な場合はREGEXREPLACE
ハイフン、空白、長音記号など複数の表記ゆれをまとめて処理したい時は、正規表現を使えるREGEXREPLACEが有利です。
たとえばB2に[- ー]+を入れ、=REGEXREPLACE(A2,B2,T(N()))とすると、複数種類の区切り文字を一括で削除しやすくなります。
SUBSTITUTEは1文字ずつ指定する必要があるため、表記ゆれが3種類を超えるならREGEXREPLACEを検討した方が早いです。スプレッドシートの検索・置換をする方法 – 便利な正規表現や …
大量データの一括処理にはGASも選択肢
数千行から数万行のデータを複数シートで処理するなら、関数を大量に並べるよりGASで一括変換した方が軽くなる場合があります。
特に、毎月同じ置換を繰り返す業務では、スクリプト化すると作業時間を数分から数十秒へ短縮しやすいです。
ただしGASは元データを書き換える設計にもできるため、最初はコピーシートで検証してから本番適用するのが安全です。
考え方をつかむなら、GASの置換解説動画が参考になります。
まとめ
SUBSTITUTE関数を使いこなすポイントは次の5つです。
基本構文は=SUBSTITUTE(文字列, 検索文字列, 置換文字列, [出現回数])第4引数を省略すると一致箇所をすべて置換空白、改行、ハイフンはCHARやUNICHARで扱うと安定複数置換はネストで対応できる複雑な表記ゆれはREGEXREPLACE、大量処理はGASが有力
まずは=SUBSTITUTE(A2,B2,C2)の基本形を1つ作り、次に空白削除や2回目だけ置換する式へ広げると、実務で迷いにくくなります。
基礎仕様を再確認したい時は、Google ドキュメント エディタ ヘルプを手元に置いておくと安心です。
コメント