【Excel関数小技】文字列の置換は関数でも出来る？―SUBSTITUTE関数の基礎から応用を解説

データベースを数多く取り扱っているビデオリサーチでは、日々データの収集や分析・解析業務を行っています。その際に欠かせないのがExcel（エクセル）です。データを扱いやすい形に整える作業は、慎重かつ効率的に進めたいものです。このシリーズでは、当社が日々の業務で使用している【エクセル関数】を使った『小技』を紹介します。

Excelで文字列を置換したいとき、Excelの「置換機能（Ctrl+H）」を使う方が大半かと思います。

もちろんExcelの置換機能を用いた文字列の置換は簡単で便利ですが、ある関数を使えば、文字列の置換ができるだけでなく、条件を付けた置換が可能です。

本記事では、そんな「文字列を置換したい」というときに便利な関数について、事例などを用いてご紹介します。

1. Excelの置換機能では難しい置換が可能になる関数「SUBSTITUTE関数」

Excelの置換機能以外で文字を置換する方法は、「SUBSTITUTE関数」です。

SUBSTITUTE関数を使うと、Excelの置換機能と同じようにセル内の文字列を別の文字列に置き換えることができます。

SUBSTITUTE関数は、Excelの置換機能とは異なり、さまざまな条件を付けた「置換」が行えます。Excelの置換機能では出来ない色々なことが可能になる、ということです。

ここで、SUBSTITUTE関数について詳しく解説していく前に、Excelの置換機能を使った置換の方法を見てみましょう。

上図は、会社情報をまとめたリストを整理する際に、セルA2にある「（株）ABCDE」という文字列の「（株）」の部分を「株式会社」に変更したいときに、Excelの置換機能を用いて文字列を置換する例です。

まず、Ctrl+Hを押すか、もしくはExcelのリボンより［ホーム］→［編集］→［検索と選択］をクリックし［置換］を選択します。

出てきた画面の
検索する文字列に「（株）」
置換後の文字列に「株式会社」
と入力し、［すべて置換］か［置換］をクリックすると、置換ができます。

SUBSTITUTE関数を用いて置換することも可能ですが、このように単純な置換であればExcelの置換機能で問題ありません。

しかしExcelの置換機能は、例えば以下のような場合では置換することが難しいです。

・元の文字列を残しておきたい場合
・条件をつけて置換をしたい場合
・一度に複数の異なる置換をしたい場合
・数式が入っているセルの文字列を置換したい場合

これらの置換が、SUBSTITUTE関数なら可能になります。
次の章からは、SUBSTITUTE関数を使用した置換方法についてご紹介していきます。

2. SUBSTITUTE関数で置換する方法

文字列の置換

先述したExcelの置換機能を使った置換の例を用いて、SUBSTITUTE関数で置換してみます。

会社情報をまとめたリストを整理する際に、セルA2「（株）ABCDE」にある「（株）」の部分を「株式会社」に変更したいという場合には以下のように表します。

＝SUBSTITUTE（A2, "（株）","株式会社"）
＝セルA2「（株）ABCDE」の文字列「（株）」を、「株式会社」に変更する
※上記のように文字列を関数に入力する際には "" で囲ってください。

例に用いているリストではC2セルに入力した数式を、オートフィル機能でC3セル以降のセルにも反映しております。（本記事内の他の例も同様にオートフィル機能を利用。）

また、SUBSTITUTE関数は以下の引数からできており、前3つの引数（オレンジ）は入力必須、最後の引数（グレー）は省略可能となります。（上記の例では前3つの引数のみを使用しました。）

＝SUBSTITUTE（文字列, 検索文字列, 置換文字列, 置換対象）

次に、SUBSTITUTE関数の全ての引数を使った例をご紹介します。

特定の文字列のみ置換

Excelの置換機能では、「複数ある文字列の中で〇番目に出現する文字列だけ置換したい」というような条件を付けることが出来ませんが、SUBSTITUTE関数ではそれが可能です。

この例では、品番をまとめたリストに新品番を追加しようとしています。
セルA2「AB123-CDAB」の、最初の「AB」のみを「YZ」に置き換えて新品番にしたい場合、以下のように表します。

＝SUBSTITUTE（A2, "AB","YZ",1）
＝セルA2の「AB123-CDAB」の、最初の「AB」のみを「YZ」に変更する

4つ目の引数「置換対象」には、変更したい文字列が何番目に出現するのかを入力します。
（最初に出現する場合は「1」、2番目に出現する場合は「2」、など...）

今回の場合、例えばA2セルの文字列には「AB」が2つ入っているため、4つ目の引数「置換対象」に「1」を入力すると１つ目の「AB」のみが置換されることになります。

もし4つ目の引数を入力しなければ、新品番が「YZ123-CDYZ」に置換されるので、置換対象の文字列の中に、同じ文字や文字列が複数ある場合は4つ目の引数を指定しましょう。

3.【応用編】SUBSTITUTE関数でより複雑な条件で置換する方法

複数の文字列を同時に置換

例えば、品番をまとめたリストで、セルA2の「AB123-CD-EF」という文字列の「AB」を「YZ」に、「CD」を「GH」に変更した新品番にしたい場合は以下のように表します。

=SUBSTITUTE(SUBSTITUTE(A2, "AB","YZ"), "CD", "GH")
＝セルA2の「AB123-CDEF」の「AB」を「YZ」に、「CD」を「GH」に変更する

SUBSTITUTE関数の引数「文字列」にSUBSTITUTE関数を組み合わせることで複数の文字列を置換できます。

条件を3つにし、「AB」を「UV」に、「CD」を「XW」に、「EF」を「YZ」に変更したい場合は以下のように置換します。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "AB","UV"),"CD","XW"),"EF","YZ")

数式を使ったセルの文字列を置換

スポーツクラブの会員を管理しているリストでは、A列の会員IDを、C列（メールアドレスの＠より前を抽出した文字列）とD列（入会日）を合わせたものにしています。
しかしマニュアル変更により、「.」（ドット）が入らない会員IDに変更しなければなりません。
セルA2「hanako.sato20240301」(=C2&D2)を、新会員ID「hanakosato20240301」にしたい場合は以下のように表します。

＝SUBSTITUTE（A2, ".",""）
＝セルA2「hanako.sato20240301」（数式...「C2&D2」）の「.」（ドット）を削除する

「.」を「""」（空白）に置換することで、「.」を消した会員IDになりました。

このようにSUBSTITUTE関数は、数式を使用したセルの文字列も置換できます。

大文字・小文字問わず文字列を置換

通常、SUBSTITUTE 関数は、同じ文字であっても大文字と小文字が統一されずに表されていると、違う文字として認識してしまいます。
例えば「English」を別の文字列に置換したい場合にSUBSTITUTE関数を使用しても、大文字の「ENGLISH」や小文字の「english」で表されている文字列は置換されません。
そこで、SUBSTITUTE 関数にUPPER関数やLOWER関数を組み合わせ、文字列の大文字・小文字を統一することで、置換できます。

この例では、とある生徒のカリキュラムのうち、「English」を「SCIENCE」に変更したいと考えています。
しかしA列には「ENGLISH」「english」「English」など、大文字や小文字がバラバラで表記が統一されていません。
そんなときには、UPPER関数を用いて以下のように表します。

=SUBSTITUTE(UPPER(A2), "ENGLISH", "SCIENCE")
=セルA2の文字列を大文字にすると「ENGLISH」になる場合は「SCIENCE」に変更する

UPPER関数を使用して文字列をすべて大文字に変換して統一し、検索文字列には大文字の「ENGLISH」と入力することで、大文字・小文字問わず置換できました。

他の関数と組み合わせて条件付きで文字列を置換

サッカーチームのメンバーを管理しているリストの中で、住んでいる市町村が東町のメンバーは新チーム「スーパーシュートFC」に入ることになり、リストを変更しなければなりません。
セルA2が「東町」であれば、チーム名をセルC2の「レインボーFC」から新チーム名「スーパーシュートFC」にしたい、という場合は以下のように表します。

=IF(A2="東町", SUBSTITUTE(C2, "レインボーFC", "スーパーシュートFC"), C2)
＝セルA2に「東町」という文字列が入っている場合にのみ、セルC2の「レインボーFC」を「スーパーシュートFC」に変更する

今回は、IF関数を用いて「東町」の人のみチーム名が変わるような数式を組んでいるため、E列では東町以外の人のチーム名はC列と同じになります。

このようにSUBSTITUTE関数は、IF関数や、先述したUPPER関数・LOWER関数などの他の関数と組み合わせることにより、より複雑な置換を効率的に行うことができます。

さいごに

いかがでしたか？
関数には無限の組み合わせや使い方があり、今回紹介した例はその一例にすぎません。
本連載は今後も不定期で更新予定です。日々の業務の効率化にお役立ていただけましたら幸いです。