文字列の置換関数として、まず思い浮かべるのは REPLACE関数ではないでしょうか。
特にプログラムを組んでいる人であれば、間違いなく REPLACE 関数で置換しようとするでしょう。しかし Excel における REPLACE 関数は、Java 等の一般的なプログラム言語のそれとは動きが異なり、思っているような置換処理をしてくれません。
例えば次のような文字列が A1 セルに入力されていたとします。
「明日は父の誕生日。早く明日にならないかな。」
この「明日」を「明後日」にする場合、REPLACE 関数は使えません。ある特定の文字列を別の文字へ置き換える場合、SUBSTITUTE 関数を利用します。
REPLACE 関数と SUBSTITUTE 関数の違い
| 関数 | 用途 |
|---|---|
| REPLACE | 文字列内の指定範囲（X文字目からY文字目）を、置換したい文字に変換する |
| 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 関数の違い、わかりましたでしょうか。文字列の内容に関係なく文字の位置で置換するのが REPLACE 関数、そして文字列の内容に対し、検索条件により置換するのが SUBSTITUTE 関数となります。
是非とも両者の違いを覚えて、用途に応じて使いこなせるようになってください。
