Excel 特定の文字列を置換(SUBSTITUTE関数)

2020年10月14日

SUBSTITUTE関数は文字列を検索し、一致すればその部分を置換する関数です。

数式の中で検索・置換と同様の働きをします。

この記事では、その使用と使い方を紹介します。

仕様

=SUBSTITUTE(文字列,検索文字列,置換文字列,置換対象)
引数省略時の値説明
文字列省略不可置換の対象とする文字列を指定。
検索文字列省略不可検索する文字列を指定。
置換文字列省略不可置換する文字列を指定。
置換対象全て置換一致する文字列の何番目を置換するかを指定。
省略の場合、全て置換。指定した場合、指定箇所のみ置換
各引数の詳細

使用例

下記の例で表示します。

パターン
1検索結果をすべて置換(置換対象を省略)
23つめの結果のみを置換
3一致するものがない
サンプルのケース一覧
SUBSTITUTE関数の使用例
SUBSTITUTE関数の使用例

一致するものがない場合、エラーとはならず元の文字列のまま表示されます。

複数の文字列を置換する方法

SUBSTITUTE関数は一つの文字列にのみ対応しています。

複数の文字列に対応するには複数、重ねる必要があります

REPLACE関数との違い

文字列を置換するREPLACE関数に似ていますが置換箇所の選び方が異なります。

関数置換箇所の選び方
SUBSTITUTE関数指定の文字列で検索
REPLACE関数開始位置と文字数を指定

スピルを利用する方法とメリット

Office365やExcel2019以降ではスピルという形式で記述可能です。

これは複数セルに数式をコピーする場合に便利な機能です。

スピルの利用には何れかの引数を複数セルの範囲で指定します。

例では全てを範囲で指定していますが、一部でも構いません。

スピルでSUBSTITUTE関数を記述する例(F3セル)
スピルでSUBSTITUTE関数を記述する例(F3セル)

数式を入力するのは最初の一つのセルですが、

引数で指定した高さ分、数式がセルのコピーなしで自動拡大されます。

F3に指定したSUBSTITUTE関数がF5まで自動拡大
F3に指定したSUBSTITUTE関数がF5まで自動拡大

スピルを利用することで今回の例では、

F3セルに入力するだけでよいため(F4以降にコピーペーストが不要)

以下のメリットがあります。

  • 入力の手間が少ない(数式のセルが多いほど効果が大きい)
  • 数式を編集した時、コピー忘れのリスクがない(最初のセルを更新すると全セルに反映)
  • 絶対参照が不要
  • 途中に行を追加した場合、数式のコピー&ペーストが不要。行削除でも壊れにくい。

関連記事

SUBSTITUTE関数の活用例

URLパラメータの値を抽出する数式

令和元年と表示する方法

プルダウンで先頭の候補に空白を入れる方法

文字列が何個含まれているかを調べる方法

FIND・FINDB関数で右(後ろ)からN文字目の検索する方法

セル内改行の挿入・削除・置換の方法

郵便番号・電話番号に-(ハイフン)を入れる方法、削除する方法

VLOOKUP関数で頻繁に発生する問題と対処法

CSVなどの文字列の分割を関数で行う方法

列番号を数字またはアルファベットで取得する方法

COUNTIFS関数でワイルドカード(*?)を文字列として扱う方法

関数の仕様と使い方の記事一覧