Excel 指定のテキストを置換(SUBSTITUTE関数)

2023年5月11日

English version.

SUBSTITUTE関数はテキストを検索して一致すれば、その部分を置換する関数です。

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

仕様

=SUBSTITUTE(文字列,検索文字列,置換文字列,置換対象)
引数省略時の値説明
文字列省略不可対象のテキスト。
検索文字列省略不可検索するテキスト。文字列の中から検索。
置換文字列省略不可置換するテキスト。文字列の中の検索文字列を置換文字列。
置換対象全て置換一致する文字列の何番目を置換するかを指定。
省略の場合、全て置換。
各引数の詳細

使用例

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

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

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

SUBSTITUTE関数は一つのテキストにのみ対応しています。

複数のテキストに対応するには複数、重ねる必要があります

REPLACE関数との違い

同じくテキストを置換するREPLACE関数に似ていますが置換箇所の選び方が異なります。

関数置換箇所の選び方
SUBSTITUTE関数テキスト
REPLACE関数開始位置と文字数

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

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

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

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

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

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

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

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

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

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

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

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

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

Excel完全マスター: 関数・テクニック・新機能を網羅した決定版ガイド

本書は、Excelの基礎から応用まで、包括的に学べるガイドブックです。当サイトの豊富な情報を、電子書籍向けに最適化し、読みやすく再構成しました。

【本書の特徴】

  • 情報量:Word-A4サイズ換算で1,400ページ相当
  • 基本操作から高度なテクニックまで、段階的に学習可能
  • 140種類の関数を詳細に解説
  • 109種類の実用的なテクニックと具体例を紹介
  • 最新機能(XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピル等)を徹底解説

【対象読者】

  • Excel初心者からプロフェッショナルまで
  • 体系的にExcelスキルを向上させたい方
  • 業務効率化を目指すビジネスパーソン
  • データ分析や可視化のスキルを磨きたい方

【本書の強み】

  • 実務に即した例題と解説
  • 視覚的な図表やスクリーンショットで理解を促進
  • 最新のExcelバージョンに対応した内容
  • 著者の長年の経験に基づく、実践的なTipsを多数収録

【更新とサポート】

  • 常に最新の情報を反映するため、定期的に内容を更新
  • 最新版の入手方法:Amazonカスタマーサポートへお問い合わせください

【入手方法】

  • 買い切り:950円
  • Kindle Unlimited:読み放題プランで利用可能

Excelマスターを目指す方、業務効率を劇的に向上させたい方に、自信を持っておすすめできる一冊です。この1冊で、Excelの可能性を最大限に引き出す力が身につきます。

関連記事

SUBSTITUTE関数の活用例

セルのテキストから複数種類の文字を削除する数式

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

令和元年と表示する方法

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

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

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

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

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

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

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

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

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

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