【関数】文字列中の指定の文字を新しい文字へ置換できる「SUBSTITUTE」の使い方
AさんAさん

手入力していくデータで「りんご」と「アップル」など、同義語で表記ゆれがある場合、いちいち置換などで直すのが面倒です・・・。
こうした場合、もっと楽に修正することができないですかね?

森田森田

その場合は、関数のSUBSTITUTEを活用すると良いですよ!
では、SUBSTITUTEの使い方について解説していきますね。

はじめに

この記事は関数の概要を把握していることが前提です。

参考記事

関数の概要については以下の記事をご参照ください。

元データの同義語の表記を統一したい場合は「SUBSTITUTE」が有効

手入力するデータは「表記ゆれ」が起きてしまうもの。

表記ゆれとは、人から見れば実質同じデータなのに、PCExcel)から見ると別の表記になってしまっていることを指します。

その一例が、同義語です。
同義語とは、「りんご」と「アップル」など、同じ意味だが表記が異なる言葉のことです。

同義語での表記ゆれがあると、これをキーにした場合の検索や集計ができない、あるいは結果が狂うというリスクがあります。

よって、事前に同義語は表記を統一することが必要です。

こんな場合、関数の「SUBSTITUTE」を使うことで、文字列中の指定の文字を新しい文字への置換を自動化し、同義語の表記を統一することが可能となります。
ちなみに、SUBSTITUTEは「サブスティチュート」と呼びます。

よって、同義語の表記を統一したい場合はSUBSTITUTEを使用していきましょう。

参考記事

文字列の位置を基準に置換したい場合は「REPLACE」を使用してください(後日記事にまとめる予定)。

SUBSTITUTEの構文

SUBSTITUTEの構文は以下の通りです。

=SUBSTITUTE(文字列,検索文字列,置換文字列,[置換対象])
文字列中の指定した文字を新しい文字で置き換えます。

引数名 必須 データ型 説明
文字列 文字列 置換対象のセル(値が文字列)を指定します。
検索文字列 文字列 「文字列」の中から置換対象の文字を指定します。
※置換前の文字
置換文字列 文字列 「検索文字列」を置き換える新しい文字を指定します。
※置換後の文字
置換対象 文字列
※実際は「数値」
「文字列」中に複数の「検索文字列」がある場合、何番目のものを置き換えるか指定します。

文字列に引数「検索文字列」が含まれない場合は、引数「文字列」に指定した値がそのまま表示されます。
引数「置換対象」を省略すると、すべての引数「検索文字列」が置換されます。
通常の「置換」機能と異なり、引数「検索文字列」にワイルドカードを指定することはできません(「*」や「?」がそのまま文字列として認識される)。

【参考】「検索と置換」ダイアログと関連付けて覚えることがおすすめ

SUBSTITUTEの引数「検索文字列」・「置換文字列」については、「置換」機能で起動する「検索と置換」ダイアログと関連付けて覚えることがおすすめです。

同じ置換なので、必要な情報は一緒だと分かります。

【参考】SUBSTITUTEは「文字列操作関数」

あくまで参考情報となりますが、SUBSTITUTEがリボン「数式」タブの関数ライブラリの「文字列操作」に分類されています。

実際にSUBSTITUTEを活用する際は、以下で解説しているように直接入力で挿入していきましょう。

SUBSTITUTEの使用結果イメージ

SUBSTITUTEで同義語の表記を統一したイメージは以下の通りです。
今回は「商品名」列の「アップル」を「りんご」へ置換しました。

なお、SUBSTITUTEは原則一つの関数で一つのセルのみが変換対象です。

ベースの数式をセットしたら、他のセルへペーストしましょう。

また、SUBSTITUTEは一つの関数で一つの文字しか置換できません。

もし、複数の文字を置換したい場合は、その種類の数だけSUBSTITUTEが必要となります。
1つ目のSUBSTITUTEの戻り値を2つ目のSUBSTITUTEの対象(引数「文字列」)にするイメージです。

参考記事

置換対象の文字の種類が多い場合は、置換対象の文字の一覧となる表を用意し、VLOOKUPで置換前の文字をキーに置換後の文字を転記した方が便利です(詳細は以下の記事を参照)。

【注意】SUBSTITUTEの対象が数値や日付/時刻でも戻り値は「文字列」になってしまう

SUBSTITUTEの引数「文字列」に指定したセルの値が数値や日付/時刻であったとしても、戻り値のデータ型は「文字列」となってしまいます。

一例として、数値の「100」のうち、「1」→「2」へ置換してみたものが以下です。

基本的にSUBSTITUTEは文字列を対象に使うことが一般的ですが、万が一数値や日付/時刻を対象にする際はご注意ください。

参考記事

データ型を「数値」にしたい場合は「VALUE」、「日付」にしたい場合は「DATEVALUE」、「時刻」にしたい場合は「TIMEVALUE」をそれぞれ併用しましょう(後日記事にまとめる予定)。

【参考】引数「置換対象」の活用イメージ

実務で使うケースはレアですが、引数「置換対象」まで指定するとどうなるかを見てみましょう。

一例として、「iPhone11」のうち「1」→「2」へ置換する際、引数「置換対象」の有無でどう変わるか、以下をご覧ください。

実際は「11」→「12」で置換すれば良いですが、実務で置換したい対象の文字が文字列中の2番目以降にある場合に活用してください。

SUBSTITUTEの数式の挿入手順

上記の結果を得るための手順は以下の通りです。

  1. 関数を挿入するセルを選択
    ※今回はH2セル
  2. =su」と入力
  3. サジェストから「SUBSTITUTE」を選択し、「Tab」キーで確定
  4. 置換対象のセルを選択
    ※今回はD2セル
  5. コンマ(,)を入力
  6. 置換前の文字を入力
  7. コンマ(,)を入力
  8. 置換後の文字を入力
  9. Enter」キーで確定
  10. 1行目の数式をコピーし、以降のセルへペースト
    ※今回はH3~H21セルへペースト

手順②の際にIMEを半角英数モードにすること。
テーブルの場合、手順⑩は不要(全レコードへ数式が自動的にコピーされる)。
手順⑥⑧で空白(ブランク)を指定したい場合はダブルクォーテーション()を2つ入力すること。

サンプルファイルで練習しよう!

可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。

サンプルファイル_ワークシート関数_SUBSTITUTE.xlsx

※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
(上記リンクから登録フォームへ遷移します)

ブックを開いたら、次の手順を実施してください。(今までの解説のまとめです)

  1. 関数を挿入するセルを選択
    ※今回はH2セル
  2. =su」と入力
  3. サジェストから「SUBSTITUTE」を選択し、「Tab」キーで確定
  4. 置換対象のセルを選択
    ※今回はD2セル
  5. コンマ(,)を入力
  6. 置換前の文字を入力
  7. コンマ(,)を入力
  8. 置換後の文字を入力
  9. Enter」キーで確定
  10. 1行目の数式をコピーし、以降のセルへペースト
    ※今回はH3~H21セルへペースト

本記事の解説と同じ結果になればOKです!

さいごに

いかがでしたでしょうか?

SUBSTITUTEは表記ゆれを修正する際に役立つ関数のうちの一つです。

定期的に同義語での表記ゆれを統一する機会があるなら、ぜひ覚えておいた方が良いですね。

なお、SUBSTITUTE以外にもExcelでのデータ整形の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。

ご参考になれば幸いですm(_ _)m

森田森田

SUBSTITUTEとセットで覚えておいた方が良い関数は、文字列の位置を基準に置換できるREPLACEです。
また、SUBSTITUTEは他の文字列操作関数と比べて引数が多いですが、「置換」機能をイメージすれば理解しやすいですよ!