SUBSTITUTE関数の使い方｜複数の文字列を一括置換する方法など

エクセルのSUBSTITUTE関数の使い方を紹介します。少し長い名前の関数ですが、文字列を任意の文字列に置換することができる便利な関数です。

使い方を工夫することでセル内にある不要な空白を消去したり、複数の文字列を一括置換することが可能ですので、ぜひ基本的な使い方から応用的な使い方までトライしてみてください。

エクセルのSUBSTITUTE関数とは

エクセルのSUBSTITUTE（サブスティチュート）関数とは、セル内の検索文字列を、指定した置換文字列に置き換えることができる関数です。

エクセルやワードにも任意の文字を検索したり置換したりする機能がありますが、この置換する機能を関数で実現することができるのです。

例えばセル内で使用されているカッコ()の記号を【】に変更したい時など、文字や記号の置き換えに有効です。

SUBSTITUTE関数の書式

エクセルのSUBSTITUTE関数の構文は「=SUBSTITUTE(文字列,検索文字列,置換文字列,[対象])」となっており、第四引数は省略可能です。詳細な引数の設定の仕方を見ていきましょう。

第一引数：文字列

第一引数は「文字列」を指定します。文字を置き換えたいテキストのあるセルを参照しましょう。例えば、A2セルにある文字を置き換えたい場合は「A2」などと入力します。

第二引数：検索文字列

次に第二引数には「検索文字列」を指定します。置き換える前の文字を入力します。「”りんご”」といったように文字をダブルクオーテーション「”」で囲むのを忘れないようにしましょう。

第三引数：置換文字列

第三引数には「置換文字列」を指定します。置き換えた後の文字を入力します。こちらも。「”バナナ”」といったように文字をダブルクオーテーション「”」で囲むのを忘れないようにしましょう。

SUBSTITUTE関数の使い方

エクセルのSUBSTITUTE関数の使い方を紹介します。まずは基本的な使い方を紹介するので、引数の指定の仕方やSUBSTITUTE関数の使いどころを見ていきましょう。

SUBSTITUTE関数の基本的な使い方

具体例を用いてSUBSTITUTE関数の基本的な使い方を覚えましょう。ここでは画像のような県名が入力されているデータを使って説明していきます。

県名が入力されているセルを対象に「県」という文字を全て「市」に変換していきます。置換後のテキストを表示させたいセルに関数を入力しましょう。例では「=SUBSTITUTE(B3,”県”,”市”)」となります。

残りのセルもオートフィルで入力しましょう。これで「県」が「市」に置き換わりました。

この方法だと、「県」という文字が含まれていれば全て置換してしまうことに注意しましょう。同じ文字が複数個セル内に含まれる場合、1つだけをを指定して置換する方法を次の章で解説していきます。

第四引数で「置換対象」を指定する方法

第四引数に「置換対象」を指定すると、セル内の中で同じ文字があっても特定の文字列のみ置換することが可能になります。

例えば画像のようなテキストがあったとします。このテキスト内には「県」という文字がたくさん出てきますが、「東京県」と書かれた「県」の文字のみ「都」に置換したいとします。

そこでテキスト内の「県」という文字を数え、「東京県」とかかれた部分の県が何番目に当たるか計算しましょう。例では2番目に出てくるのでSUBSTITUTE関数の第四引数に「2」と指定します。

これで2番目の「県」のみを特定して置換することができました。

SUBSTITUTE関数の応用的な使い方

ここからは、エクセルのSUBSTITUTE関数の応用的な使い方を紹介していきます。複数の文字列を一括で置換したり、削除したりする方法を覚えておけば、効率よくテキストを整えることができるようになります。

複数の文字列を一括で置換する方法

複数の文字列を一括で指定したい場合、関数を入れ子にすれば実現可能です。ただし、置換したい文字列が増えれば増えるほど複雑になりますのでミスをしないようにしましょう。

ここでは条件が2つ（置換したい文字列が2つ）の場合のやり方を解説します。「（」と「）」の記号をそれぞれ「【」と「】」に置き換えます。画像のように、1つ目のSUBSTITUTE関数の第一引数に2つ目のSUBSTITUTE関数を入れて、「=SUBSTITUTE(SUBSTITUTE(B3,”（”,”【”),”）”,”】”)」と記述しましょう。

これで置換操作が2回繰り返されたことになり、「（」と「）」の記号をそれぞれ「【」と「】」に一括で置換することができました。

条件が増える度に入れ子構造を増やしていけば、原理的には任意の種類の文字列を一括で置換することが可能です。

複数の文字列を一括削除する方法

先ほど紹介した「複数の文字列を一括で置換する方法」を応用してみましょう。ここでは、画像中のハイフン「ー」や「-」を一括削除します。

関数を入れ子にして記述します。1つ目のSUBSTITUTE関数の第一引数に2つ目のSUBSTITUTE関数を入れましょう。今回は文字列を削除したいので、どちらの関数の第三引数も空白「””」と指定しましょう。この時ダブルクオーテーションの間にスペースを入れたりしないようにしてください。置換前の文字列があった箇所にスペースが挿入され、単なる削除ではなくなってしまいます。

残りのセルもオートフィルで入力しましょう。これで複数の文字列を一括で削除することができました。

セルの空白を一括削除する方法

セルの空白（スペース）を一括削除することも可能です。こちらも先ほど紹介した「複数の文字列を一括で置換する方法」を応用してみましょう。

空白（スペース）の指定の仕方は「” ”」や「”　”」などのように文字列の代わりにスペースを入力します。スペースには半角と全角があるので、どちらも指定しておくとよいでしょう。

SUBSTITUTE関数を入れ子にして、半角スペースと全角スペースを「文字列なしの空白」に置換（削除）します。第二引数と第三引数を画像のように指定して、「=SUBSTITUTE(SUBSTITUTE(B3,”-“,””),”ー”,””)」と入力してください。

残りのセルもオートフィルで入力しましょう。これでセルの空白（スペース）を一括削除することができました。

改行を一括削除する方法

改行も工夫するとSUBSTITUTE関数で取り扱うことができますが、どのように指定すればよいのでしょうか？

改行を文字列として扱うにはCHAR関数を使用します。CHAR関数は文字コードを文字に変換する場合に使用される関数で、引数に「10」を指定すると「改行」を文字として扱うことができます。

一括削除するには、SUBSTITUTE関数の第二引数に「CHAR(10)」と指定しましょう。ダブルクオーテーション「”」で囲む必要はありません。また第三引数には空白「””」と指定して「=SUBSTITUTE(B3,”CHAR(10)”,””)」と入力してください。

これで改行を削除することができました。

SUBSTITUTE関数で多いエラーと対処方法

エクセルのSUBSTITUTE関数でよく出現するエラーとその対処方法を説明します。SUBSTITUTE関数を使っていて行き詰ってしまった場合は、この箇所を参考にしながら原因を探っていきましょう。

第三引数で指定した文字列に置換されない

第三引数で指定した文字列にうまく置換されない場合があります。原因としては、うまく引数が指定できていないことが考えられます。

以下の例では、第二引数と第三引数の間のカンマが抜けています。

対処方法としては、きちんと書式に沿って正しい関数を記述することです。ここでは第二引数と第三引数の間にカンマを挿入することで誤りを訂正することができます。

第二引数と第三引数がそれぞれ正しく指定され、意図した文字に置換されました。

「#NAME?」エラーが表示される

「#NAME?」エラーが表示される時の最も多い原因として、数式や関数のスペルミスがあります。

以下の例では「SUBSTITUTE」と書くべきところが「SUBTITUTE」となっています。

SUBSTITUTE関数は名前が長いので、スペルミスをしやすい関数です。よくタイプミスをする人は、なるべく数式オートコンプリートの機能を使って手動入力を避けるとよいでしょう。

今回の例では「SUBSTITUTE」と修正すれば置換が正しく実行されます。

検索文字列や置換文字列を「”」で囲むのを忘れた場合も「#NAME?」が表示されますので気をつけましょう。

指定した文字列が表示されず空白になる

関数には問題がないのに、関数を入力したセルが空白になってしまうことがあります。この場合、第一引数で置換したいテキストがあるセルの参照を間違っている可能性があります。

以下の例では、対象となるセルがズレて元々空白のセル「B4」を参照してしまっています。

修正方法は、ずばり正しいセルを参照することです。今回は第一引数を「B3」と修正すればOKです。

これで空白になる現象が解消され、正しい置換結果が表示されました。

エクセルのSUBSTITUTE関数についておさらい

エクセルのSUBSTITUTE関数は、文字を任意の文字列に置換したい時に効果を発揮する関数です。

マクロや関数など高度な機能を使っていると、テキスト内に空白や改行がある場合にうまく処理できないことがあります。また全角を全て半角に変換したい、ハイフンを全て削除したいなどという場面もあるでしょう。

このような時にSUBSTITUTE関数を使えば効率よくテキストを整形できるので、工夫しながらどんどん活用してみてください。