関数で置換するならSUBSTITUTE関数！複数置換や関数に埋め込むことも可能で万能！

SUBSTITUTE関数で置換する方法

指定した文字列を指定した文字列に置き換えてくれる関数です。

これだけを聞くと「エクセル機能の置換機能と何が違うの？」と思われると思います。置換機能との違いは最後にご紹介するとして、まずは実際にSUBSUTITUTE関数を組んでいきましょう。

練習用エクセルファイルを用意したので、必要な方は下記よりダウンロードしてくださいね。

置換対象が1つの場合

B列の「マウスの商品コード」を一部変更してC列に新しい商品コードを表示させていきます。

C列に新しい商品コードを表示させたいのでC2セルにSUBSTITUTEを入れていきます。= を入力しSUと入力すると候補が出るので、その中からSUBSTITUTEを選択し Tab を押します。

次に置換したい文字列が入っているセルを選択します。今回はB列に入ってる商品コードが対象なのでB2セルを選択しカンマ , を打ちます。

次に置換したい文字列を指定します。数式内の文字列はダブルクオーテーション「””」で囲むルールがあるので、忘れずにダブルクオーテーション “ で囲みましょう。
今回はマウスの商品コードのうち「M」が置き換え対象なので “M” と入力しカンマ , を打ちます。

最後に置換する文字列を指定します。マウスの商品コード「M」を「MS」に置き換えるので、「MS」をダブルクオーテーション “ で囲んで “MS” と入力し最後にカッコを閉じたら Enter を押して完成です。

C2セルに新しい商品コード「MS-001」が表示されましたね。

後はC2セルの右下角にある緑の■（フィルハンドル）をダブルクリックし、オートフィルで全てに反映させて終了です。

今回は商品コードを文字列としてダブルクオーテーション “ で囲んで指定しましたが、置換対象文字列はF2セル、置換する文字列をG2セルと指定しても問題ありません。その際は忘れずに必ず絶対参照を付けておきましょう。

=SUBSTITUTE(対象セル番地,"置換前の文字列","置換後の文字列")
=SUBSTITUTE(B2,"M","MS")

=SUBSTITUTE(対象セル番地,"置換前のセル番地","置換後のセル番地")
=SUBSTITUTE(B2,$F$2,$G$2)

置換対象が複数の場合

B列の「マウスの商品コード」と「キーボードの商品コード」両方の一部を一度に変更してC列に新しい商品コードを表示させていきます。

C列に新しい商品コードを表示させたいのでC2セルにSUBSTITUTEを入れていきます。= を入力しSUと入力すると候補が出るので、その中からSUBSTITUTEを選択し Tab を押します。

今回は複数の置き換えを一度に行うので、ここでもう一つSUBSTITUTE関数を入れます。これを「入れ子」または「ネスト」と言います。

SUBSTITUTEの中にSUBSTITUTEを入れたら、最初の置き換えたい文字列を指定します。置き換えたい文字列はB列の商品コードなのでB2セルを選択しカンマ , を打ちます。

最初の置換したい文字列として「M」を指定していきます。文字列なのでダブルクオーテーション “ で囲み“M”と入力しカンマ , を打ちます。

次に、置換する文字列「MS」を指定します。こちらも先ほど同様にダブルクオーテーション “ で囲み“MS”と入力しカッコを閉じます。これで一つ目の置換に対するSUBSTITUTEの入力は終わったので、カンマ , を打ちます。

次に二つ目の文字列を指定していきます。置換対象文字列が入ってるセルはB列で最初に指定しているので、ここでの指定は不要です。

置換対象文字列である「K」をダブルクオーテーション “ で囲み“K”と指定しカンマ , を打ちます。
そして置換する文字列である「KB」をダブルクオーテーション “ で囲み“KB”と入力したらカッコを閉じて Enter で確定し終了です。

C2セルに新しい商品コードが反映されたのを確認したら、C2セル右下角の緑の■をダブルクリックし、オートフィルで全てに反映させます。

マウスの商品コードが全て「MS」に、キーボードの商品コードが全て「KB」になりましたね。

=SUBSTITUTE(SUBSTITUTE(対象セル番地,"置換前文字列①","置換後文字列①"),"置換前文字列②","置換後文字列②")
=SUBSTITUTE(SUBSTITUTE(B2,"M","MS"),"K","KB")

=SUBSTITUTE(SUBSTITUTE(対象セル番地,"置換前セル番地①","置換後セル番地①"),"置換前セル番地②","置換後セル番地②")
=SUBSTITUTE(SUBSTITUTE(B2,$F$2,$G$2),$F$3,$G$3)

置換対象の位置を指定する

SUBSTITUTE関数では同一の置換対象が複数あった場合に、置換対象の位置を指定することが可能です。下記の例題を使って見ていきましょう。

C列に「3つ目のハイフンをピリオドに置換」していきます。

C2セルに = を入力しSUと入力すると候補が出るので、その中からSUBSTITUTEを選択し Tab を押します。

次に置換したい文字列が入っているセルを選択します。今回はA列に入ってる商品番号が対象なのでA2セルを選択しカンマ , を打ちます。

続けて検索文字列を入力します。今回はハイフンなのでダブルクォーテーション “ で「 –」 を囲んで「”-”」と入力したらカンマ , を打ちます。

次に置換文字列を入力します。今回はピリオドに置換したいのでダブルクォーテーション “ で「 .」 を囲んで「“.”」と入力しカンマ , を打ちます。

最後に置換対象で置換位置を指定します。今回は3つ目のハイフンをピリオドに置換したいので「3」と入力したらカッコを閉じて Enter を押して関数を完成させます。

3つ目のハイフンをピリオドに置換されました。

後はオートフィルで最後までSUBSTITUTE関数を反映させれば完成です。

=SUBSTITUTE(対象セル番地,"置換前の文字列","置換後の文字列",何個目の置換対象文字列か指定)
=SUBSTITUTE(A2,"-",".",3)

=SUBSTITUTE(対象セル番地,"置換前の文字列","置換後の文字列",何個目の置換対象文字列か指定)
=SUBSTITUTE(A2,"-","",2)

置換機能との違い

冒頭でも少し触れましたが、【置換する】というとエクセル機能の置換機能が思い浮かぶと思います。
ここで置換機能との違いをいくつか簡単にご紹介します。

置換結果の表示場所が違う

置換機能では、現在入ってるデータを上書き置換するのに対して、SUBSTITUTE関数では別セルに置換結果を表示させます。

置換機能では数式の置換が可能

置換機能では、選択した箇所の文字列だけでなく数式内も置換が可能なのに対して、SUBSTITUTE関数は文字列のみが置換対象となります。

一度に複数の置換が可能

今回のように「マウスの商品コード」と「キーボードの商品コード」のように複数の置換を行いたい場合、置換機能の場合だと、それぞれ別々に置換を行う必要があります。

新たに追加された文字列もオートフィルで反映するだけで置換可能

例えば、CSVで何かのデータを貼付し加工する場合など、後から後からCSVでデータが追加されることがあります。この場合、置換される前の文字列含まれているケースも多いと思いますが、置換機能の場合は、新しいデータに置換前の文字列が含まれていると、毎回新しい文字列に置換する必要があります。

対してSUBSTITUTE関数の場合、新しくデータが追加されても、既にあるデータからオートフィルでSUBSTITUTE関数を反映させるだけで置換してくれます。

まとめ

文字列の置換をしてくれるSUBSTITUTE関数についてご紹介しました。

エクセルの置換機能と似ているようで異なる関数なので、その時の状況に応じてSUBSUTITUTE関数と置換機能を使い分けて上手に効率アップにつなげましょう。