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

2016/12/07

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

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

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

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

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

REPLACE 関数と SUBSTITUTE 関数の違い

関数用途
REPLACE文字列内の指定範囲(X文字目からY文字目)を、置換したい文字に変換する
SUBSTITUTE文字列内の指定条件の文字を、置換したい文字に変換する

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

REPLACE 関数の利用例

文字列の中身に関係なく置換をしたい場合に利用します。例えば次のようなコードの一覧があるとします。

replace-characters-by-substitute01

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

replace-characters-by-substitute02

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

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

replace-characters-by-substitute03

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

SUBSTITUTE 関数の利用例

文字列内にある指定した文字に対して置換をしたい場合に利用します。例えば次のようなコードの一覧があるとします。

replace-characters-by-substitute04

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

replace-characters-by-substitute05

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

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

replace-characters-by-substitute06

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

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

replace-characters-by-substitute07

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

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

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

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

まとめ

以上、REPLACE 関数と SUBSTITUTE 関数の違い、わかりましたでしょうか。文字列の内容に関係なく文字の位置で置換するのが REPLACE 関数、そして文字列の内容に対し、検索条件により置換するのが SUBSTITUTE 関数となります。

是非とも両者の違いを覚えて、用途に応じて使いこなせるようになってください。

Name :
パソコンを使う上で、Excelは様々な用途で用いられますが、その分困ることもたくさんあります。
困った時に少しでも役立てるようにと、このサイトを立ち上げました。分かりやすくをモットーに解説をしていくつもりですので、どうぞよろしくお願いします。
尚、このサイトでの説明は、主に Excel2013 を利用していますので、その点につきましてはご了承いただければと思います。

Excel のフィルタ機能で空白セルがある途中までしかフィルタ出来ない場合の対処法
2016/01/12

Excel のフィルタ機能で空白セルがある途中までしかフィルタ出来ない場合の対処法

Excel 関数を用いて数値から整数部分と小数部分を抜き出す方法
2015/10/04

Excel 関数を用いて数値から整数部分と小数部分を抜き出す方法

使う分だけ覚えよう!Excel ショートカット一覧
2015/09/02

使う分だけ覚えよう!Excel ショートカット一覧

生年月日と現在の日付または指定日から年齢を計算する方法
2015/09/01

生年月日と現在の日付または指定日から年齢を計算する方法

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

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

列を再表示するショートカットキー Ctrl + Shift + 0 が機能しない場合の対処法
2015/06/15

列を再表示するショートカットキー Ctrl + Shift + 0 が機能しない場合の対処法

セルにレ点を入力する方法
2015/04/27

セルにレ点を入力する方法

複数の列に一致する条件で行数をcountする方法 AND編
2015/04/24

複数の列に一致する条件で行数をcountする方法 AND編

セルに入力した文字数を数える方法
2015/04/21

セルに入力した文字数を数える方法

SEだったら絶対に覚えておくべき5つの Excel ショートカットキー
2015/04/21

SEだったら絶対に覚えておくべき5つの Excel ショートカットキー

→もっと見る

PAGE TOP ↑