【Googleスプレッドシート】SUBSTITUTE関数で文字列の置換!複数回置換の指定方法

Googleスプレッドシートで特定の文字列を別の文字列に置き換えたい場面は多いでしょう。例えば、住所の「市」を「区」に変えたり、商品コードのハイフンを削除したりする作業です。手動で1つずつ修正すると時間がかかりますが、SUBSTITUTE関数を使えば一瞬で処理できます。この記事では、SUBSTITUTE関数の基本的な使い方から、特定の出現回数だけ置換する方法、複数の文字列をまとめて置換する応用テクニックまで詳しく解説します。

SUBSTITUTE関数の概要とできること

SUBSTITUTE関数は、指定した文字列内の特定の文字列を別の文字列に置き換えるための関数です。この関数の特徴は、置換する対象を「出現番号」で細かく制御できる点です。また、大文字と小文字を厳密に区別します。基本的な使い方は簡単で、文字列、検索文字列、置換文字列の3つの引数が必要です。4つ目の引数に出現番号を指定すると、その順番の出現だけが置換されます。すべての出現を置換したい場合は出現番号を省略します。この関数はテキストデータの整形やクレンジングに非常に便利です。

SUBSTITUTE関数の基本操作手順

以下では、具体的な例を使ってSUBSTITUTE関数の操作手順を説明します。例として、セルA1に「123-456-7890」という文字列が入っているとします。このハイフンをすべて削除したい場合の手順です。

すべての出現を置換する基本の方法

1. 置換結果を表示するセルを選択します
   例えばB1セルをクリックします。
2. 次の数式を入力します
   =SUBSTITUTE(A1, “-“, “”) と入力してEnterキーを押します。これでA1内のすべてのハイフンが削除され、「1234567890」と表示されます。
3. 数式を他のセルにコピーします
   B1セルの右下隅をダブルクリックするか、ドラッグして範囲を広げます。これで列全体のハイフンが一括削除されます。

特定の出現回数だけ置換する方法

出現番号を指定することで、n番目に現れる文字列だけを置換できます。例えば、2番目のハイフンだけを削除したい場合です。

1. 結果を表示するセルを選択します
   C1セルをクリックします。
2. 次の数式を入力します
   =SUBSTITUTE(A1, “-“, “”, 2) と入力します。この場合、A1の「123-456-7890」の内、2番目のハイフン(456と7890の間)が削除され、「123-4567890」と表示されます。
3. 出現番号を変えて試します
   出現番号を3にすると3番目のハイフンが削除されて「123-456-7890」になります。出現番号は1から始まります。

複数の異なる文字列を連続して置換する方法

SUBSTITUTE関数をネストすることで、複数の文字列を一度に置換できます。例えば、住所の「市」を「区」に、「町」を「丁目」に変更する場合などです。

1. 結果を表示するセルを選択します
   D1セルをクリックします。
2. 次の数式を入力します
   =SUBSTITUTE(SUBSTITUTE(A1, “市”, “区”), “町”, “丁目”) と入力します。内側のSUBSTITUTEで「市」を「区」に置換し、その結果に対して外側のSUBSTITUTEで「町」を「丁目」に置換します。
3. 必要に応じてさらにネストします
   置換したい文字列が3つ以上ある場合も同様にネストを続けます。ただし、ネストが深くなると数式が複雑になるので注意してください。

SUBSTITUTE関数使用時の注意点とよくあるトラブル

大文字と小文字は区別される

SUBSTITUTE関数は大文字と小文字を区別します。そのため、「abc」を検索する場合、「ABC」は置換されません。大文字小文字を無視したい場合は、UPPER関数やLOWER関数で統一するか、REGEXREPLACE関数を使用します。

出現番号を指定しても置換が行われない場合

出現番号を指定したのに置換が行われない場合、その出現番号が存在しない可能性があります。例えば、文字列内にハイフンが2つしかないのに出現番号3を指定すると、元の文字列がそのまま返されます。また、検索文字列が見つからない場合も同様です。

置換後の文字列の長さに注意

置換文字列が検索文字列より長い場合、結果の文字列は長くなります。セルの文字数制限(50,000文字)を超えないよう注意してください。特に大量のデータを処理する場合は、事前に文字数を確認することをおすすめします。

数式のネストが深くなりすぎないようにする

複数のSUBSTITUTEをネストするとき、ネストが4〜5段階を超えると数式が読みにくくなります。そのような場合は、中間結果を別の列に出力するか、REGEXREPLACE関数やQUERY関数の利用を検討します。

SUBSTITUTE関数とREPLACE関数の違い

文字列置換にはREPLACE関数もあります。両者の違いを比較表にまとめました。

関数	置換の基準	使用例
SUBSTITUTE	検索文字列で指定(内容ベース)	=SUBSTITUTE(A1,”-“,””) でハイフンをすべて削除
REPLACE	開始位置と文字数で指定(位置ベース)	=REPLACE(A1,5,3,””) で5文字目から3文字を削除

SUBSTITUTEは「何を」置き換えるかで指定するのに対し、REPLACEは「どこを」置き換えるかで指定します。用途に応じて使い分けましょう。

まとめ

SUBSTITUTE関数を使うと、特定の文字列を簡単に置換できます。出現番号を指定すれば、n番目の出現だけを狙って置換できるため、データ整形の幅が広がります。また、関数をネストすることで複数の異なる文字列を一度に置換することも可能です。まずは簡単な例で試し、徐々に応用していくことをおすすめします。さらに高度なパターン置換が必要な場合は、REGEXREPLACE関数も検討してみてください。