【Excel初心者向け】SUBSTITUTE関数

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

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

SUBSTITUTE関数_練習用

置き換える対象が1つの場合
置き換える対象が複数の場合
置換機能との違い
まとめ

置き換える対象が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(B2,"M","MS")
=SUBSTITUTE(B2,$F$2,$G$2)

置き換える対象が複数の場合

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

今回は複数の置き換えを一度に行うので、ここでもう一つSUBSTITUTE関数を入れます。これを「入れ子」または「ネスト」と言います。 SUBSTITUTEの中にSUBSTITUTEを入れたら、最初の置き換えたい文字列を指定します。置き換えたい文字列はB列の商品コードなのでB2セルを選択しカンマ , を打ちます。

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

次に二つ目の文字列を指定していきます。置き換え対象の文字列が入ってるセルはB列で最初に指定しているので、ここでの指定は不要です。置き換えられる文字列である「K」をダブルクオーテーション「“”」で囲み“K”と指定しカンマ , を打ちます。そして置き換える文字列である「KB」をダブルクオーテーション「“”」で囲み“KB”と入力したらカッコを閉じて Enter で確定し終了です。

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

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

=SUBSTITUTE(SUBSTITUTE(B2,"M","MS"),"K","KB")
=SUBSTITUTE(SUBSTITUTE(B2,$F$2,$G$2),$F$3,$G$3)

置換機能との違い

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

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

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

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

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

一度に複数の置換が可能

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

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

例えば、CSVで何かのデータを貼付し加工する場合など、後から後からCSVでデータが追加されることがあります。この場合、置換される前の文字列含まれているケースも多いと思いますが、置換期昨日の場合は、新しいデータに置換前の文字列が含まれていると、毎回新しい文字列に置換する必要があります。対してSUBSTITUTE関数の場合、新しくデータが追加されても、既にあるデータからオートフィルでSUBSTITUTE関数を反映させるだけで置換してくれます。