この記事のポイント
Excel（エクセル）の関数を使って文字列を検索して、別の値に変換したいことがあります。
文字列の置換をエクセル関数から「SUBSTITUTE関数」を使った方法をご紹介します。
ちなみに、SUBSTITUTEは「置換」の意味があります。
エクセルにはSUBSTITUTE関数に似たような関数として、REPLACE関数もあります。
どちらも任意の文字を、別の文字に置換できますが、細かい部分で違いがあるのでエクセルでの文字変換をしっかり覚えていきましょう。
置換できる関数の中には、大文字を小文字に変換するLOWER関数、半角を全角に変換するJIS関数もあるので覚えておくと良いかも知れませんね。
目次
SUBSTITUTE関数ってなに？自由に文字の置き換えができる関数
エクセルのSUBSTITUTE（サブスティチュート）関数は、文字列を別の文字列に置き換えることができる関数です。
エクセルでは関数を使わなくても「検索と置換」の機能を使えば、文字列の置き換えは可能です。
「検索と置換」の機能では「検索する文字列」と「置換後の文字列」を指定して、置換ボタンをクリックすれば文字の置き換えができてしまいますね。
では、どんな時にSUBSTITUTE関数が役立つのでしょうか？
SUBSTITUTE関数のメリット
- 置換する前の文字列を残しておきたい
- 置換する文字列が複数ある場合、2つ目だけを置換したい
こんな時にSUBSTITUTE関数が最大限に発揮できます。
今回はSUBSTITUTE関数の使い方を解説しますので、様々な置換パターンを覚えていきましょう。
SUBSTITUTE関数の使い方は？書式と引数を解説
SUBSTITUTE関数は文字列を別の文字列に置換する関数です。
それではSUBSTITUTE関数を使うため、引数について解説しますね。
SUBSTITUTE関数の書式
SUBSTITUTE関数の書式はこちらです。
関数の書式
SUBSTITUTE(文字列,検索文字列,置換文字列,[置換対象])
SUBSTITUTE関数の引数について解説します。
関数の引数
- 「文字列」には、置換したい文字のあるセルを指定します。
- 「文字列」に文字を直接入力することができます。
- 「検索文字列」は置換前の文字列を指定します。
- 「置換文字列」は置換した後の文字列を指定します。
- 「置換対象」は「検索文字列」が複数ある場合、何個目を置換するかを指定します。
- 「置換対象」は省略が可能で、省略した場合は検索した文字列をすべて置換します。
SUBSTITUTE関数を使うには？実例を使って解説
関数を使うための基本的な操作は、「関数の挿入」を使うことになります。
関数の挿入を使ってSUBSTITUTE関数を使うには、タブの真下にある「fx」をクリックする必要があります。
キーボードから直接入力することも可能ですが、まずは関数の呼び出し方や使い方に慣れることを優先しましょう。
関数の挿入からSUBSTITUTE関数を選択
関数の挿入からSUBSTITUTE関数を使って、文字列を置換してみましょう。
関数の使い方
- C5セルをクリック
- 「fx」をクリック
- 関数の検索をしましょう。
SUBSTITUTE関数の検索方法として
関数の検索方法
- 「関数の検索」に「SUBSTITUTE」を入力して検索
- 「関数の分類」に「文字列操作」を選択
どちらかの方法を使うことで「関数名」に候補となる関数の一覧が表示されますので、「SUBSTITUTE」を選択してOKボタンを押します。
SUBSTITUTE関数の引数の設定
SUBSTITUTE関数の引数の画面に移動しましたら、関数の引数には以下の値を設定しましょう。
引数の設定
- 「文字列」に「B5」を入力
- 「検索文字列」に「"（株）"」を入力
- 「置換文字列」に「"株式会社"」を入力
SUBSTITUTE関数を使うことで、社名の「（株）」表記から「株式会社」に置換した「株式会社とやま工業」を表示することができました。
記号や改行は置換できるの？SUBSTITUTE関数を使って特殊記号を置換する方法
SUBSTITUTE関数を使ってアルファベット、ひらがな、カタカナ、漢字の変換は問題なくできると思います。
ですが、記号や改行を置換しようとしたときに、「あれ？どうしたら良いんだろう？」と悩んでしまいますよね。
困ってしまったパターンを、実例を使って一部ご紹介しましょう。
SUBSTITUTE関数で「"」を置換するには？CHAR関数を使って置換
エクセルで関数を使いこなせている人が、SUBSTITUTE関数を使って「"（半角のダブルクォーテーション）」を置換しようとしたときにハマる罠があります。
SUBSTITUTE関数で「”」から「+」に変換したい場合、このように使ってしまうためです。
「"」を置換するときの注意点
=SUBSTITUTE(B3,""","+")
このようにSUBSTITUTE関数を使うと「この数式には問題があります。」とエラーが表示されてしまいました。
エクセルの関数では「"」は文字をくくるために使用するため、開始の「"」と終了の「"」が2つで1セットになります。
そのため関数で「”」の数は偶数個になるのが正しいので、「この数式には問題があります。」というエラーが出てしまう訳です。
エラーを回避するためには、「検索文字列」を「""""」（「"」を4つ）とすれば問題ありません。
「"」を置換する方法
=SUBSTITUTE(B3,"""","+")
分かりにくいですが、「"」を置換することができました。
実はCHAR（キャラクター）を使うことで、「"（ダブルクォーテーション）」をあらわすことができます。
CHAR関数は記号や特殊文字を内部コードに対応する文字に変換するもので、「"」は「CHAR(34)」で置き換えが可能です。
「CHAR(34)」で置き換えが可能
=SUBSTITUTE(B3,CHAR(34),"+")
このようにSUBSTITUTE関数を使うことで、「"」を分かりやすく置換ができますね。
改行を消すにはどうしたら良いの？SUBSTITUTE関数で改行を置換する方法
エクセルのセルの中で、改行されていることもあるでしょう。
改行をなくしたい場合にも、SUBSTITUTE関数が役立ちます。
実は改行もCHARを使ってあらわすことができ、「CHAR(10)」を置き換えてしまえば改行がなくなります。
改行を置換する方法
=SUBSTITUTE(B4,CHAR(10),"")
CHARの数値が気になる！CHAR関数のコードはCODE関数から算出可能
「"」や「改行」をCHARで表現しましたが、CHARで使用している数値が気になる人もいます。
実は、エクセルの関数を使って知る方法があります。
CODE関数です。
「"」の数字を知りたければ、このように使ってみて下さい。
=CODE("""")
「34」が表示されました。この「34」をCHAR関数に使って置換していたわけです。
では、改行を調べる方法はどうしたら良いでしょうか？同じようにCODE関数を使います。
改行をCODE関数を使用
=CODE("
")
関数の途中で改行されているため、ちょっと不思議な使い方ですが「10」が表示されます。
CODE関数で調べるのが面倒な人は、ASCIIコード表とインターネットで検索してみましょう。
数字と文字の対応表が表示されますので、そちらを参考にしてみるのもアリだと思いますよ。
SUBSTITUTE関数の第4引数を省略しないで使うには？
「文字列」に含まれる「検索文字列」を、全て「置換文字列」に置き換える場合は、第4引数「置換対象」を省略していました。
SUBSTITUTE関数の「置換対象」を指定することで、複数の「検索文字列」から何番目の文字列を置換するかを指定できます。
例えば「富山県富山市」の2つ目の「富山」に対して、「高岡」に置き換えたい場合に、「置換対象」に「2」を指定しましょう。
２つ目を置換する方法
=SUBSTITUTE(B6,"富山","高岡",2)
「富山県高岡市」とすることができました。
同じ文字列が続くときに、置換したい文字列が何番目か分かっている場合は「置換対象」を指定しましょう。
エクセルのSUBSTITUTE関数まとめ
Excel(エクセル）のSUBSTITUTE（サブスティチュート）関数を使うことで、文字列の置換ができました。
元のデータを残しておきたい場合や、置換したい文字が複数あるときに、SUBSTITUTE関数の置換の威力が発揮されます。
今回はご紹介したSUBSTITUTE関数は、エクセルの機能の「検索と置換」では上手くいかない場合に使いましょう。
途中でCHAR関数やCODE関数についてもお話しましたが、使う機会は少ないのでご安心下さい。
まずはSUBSTITUTE関数の使い方から覚えてみて下さいね。