文字列全体に対して複数の置換を行いたいなら REPLACE ではなく SUBSTITUTE 関数を使おう

文字列の置換関数として、まず思い浮かべるのは REPLACE 関数ではないでしょうか。

しかし Excel における REPLACE 関数は、Java のような一般的なプログラム言語のそれとは動きが異なり、思っているような置換処理をしてくれません。

例えば次のような文字列が A1 セルに入力されていたとします。

「明日は彼女の誕生日。早く明日にならないかな。」

この「明日」を「明後日」にする場合 REPLACE 関数は使えません。ある特定の文字列を別の文字へ置き換える場合は、SUBSTITUTE 関数を利用します。

REPLACE 関数と SUBSTITUTE 関数の違い

REPLACE 関数の場合、文字列の中身がどうであれ、指定した範囲の文字を強制的に置き換えます。一方 SUBSTITUTE 関数の場合は、文字列の中を検索条件で検索して、一致する場合にその条件を指定の文字列に置き換えます。

REPLACE 関数の利用例

文字列の中身に関係なく置換をしたい場合に利用します。例えば、次のような表をご覧ください。

頭3桁のアルファベットを一律「XYZ」に変更する場合、次のように REPLACE 関数を仕掛けます。

引数の設定値を読み解くと、A2 セルの文字列の 1文字目から 3文字目までを、"XYZ" へ置き換える処理となります。

3行目以降も同様に関数を指定することで、全て同じ処理が行われます。実際に置換した結果が次の画像です。分かりやすく C列へ B列に設定した計算式を記載しておきます。

置換元の文字列が ABC だろうが、CCD だろうが関係なくXYZ へ置換しています。この置換元の文字列の内容に依存せずに置換処理を行うのが REPLACE 関数の特徴となります。

SUBSTITUTE 関数の利用例

文字列内にある指定した文字に対して置換をしたい場合に利用します。先程と同じ表を例に説明しましょう。

このコード値内の文字「CD」を全て「ZZZ」に変更する場合、次のように SUBSTITUTE 関数を仕掛けます。

引数の設定値を読み解くと、A9 セルの文字列内にある文字列 "CD" を全て "ZZZ" へ置き換える処理となります。

他の行も同様に関数を指定することで、全て同じ処理が行われます。実際に置換した結果が次の画像です。分かりやすく C列へ B列に設定した計算式を記載しておきます。

文字列 "CD" を全て "ZZZ" へ置き換えるので、例えば A9 セルのように "CD" が 2ヶ所含まれる場合は、それぞれ "ZZZ" へ置換される仕組みとなります。

例示の計算式には設定していませんが、SUBSTITUTE 関数にもう一つ引数 (省略可) が存在しています。一番最後に付け加える引数なのですが、何番目に検索がヒットした文字を変換するか置換対象を指定することができます。実際に指定したものが次の画像です。

B9 に仕掛けた関数は、次のような内容になります。

A9 セルの文字列内にある文字列 "CD" を "ZZZ" へ置き換えますが、処置換対象は 2番目にヒットした文字列のみ。

つまり2回目に登場した後ろにある "CD" だけが "ZZZ" に置換されています。それ以外の 10行目から 13行目は、"CD" が1つしか含まれていないので、置換されずそのまま出力されています。

この置換元の文字列に対して、指定した文字列の条件を基に置換処理を行うのが SUBSTITUTE 関数の特徴となります。

まとめ

以上、REPLACE 関数と SUBSTITUTE 関数の違いでした。

是非これら関数の違いを覚えて、用途に応じて使いこなせるようになってください！