Excel（エクセル）SUBSTITUTE関数の使い方｜複数の文字列の置き換えもできる
使用関数：SUBSTITUTE
SUBSTITUTEとは？
SUBSTITUTEの読み方と意味
SUBSTITUTEは、サブスティテュートと読みます。
交代要員や代替のことを「サブ」と言いますが、これはSUBSTITUTEの略です。
SUBSTITUTEは置換を意味し、 その意味通り、特定の文字列を他の文字列に置き換える関数です。
REPLACEとの違い
文字列を置換する関数にはSUBSTITUTEの他にもう一つREPLACEがあります。
検索文字列の指定の違い
REPLACE関数が置き換える位置と置き換える文字数で検索文字列を指定するのに対して、SUBSTITUTEは引数「検索文字列」でストレートに指定します。以下はREPLACEとSUBSTITUTEの数式の比較です。
複数の置換への対応の違い
元の文字列に複数の検索文字列が含まれていた場合、REPLACE関数は位置と文字数の指定でどれか一つの検索文字列に対して置換を実行します。言い換えると、複数の文字列を一括で置換することはできません。
SUBSTITUTE関数は元の文字列に含まれる全ての検索文字列をまとめて置き換えすることが可能です。
REPLACE関数の使い方はこちらで詳しく解説しています
SUBSTITUTE関数の構成
SUBSTITUTEは「数式」タブ→「関数ライブラリ」→「文字列操作」に格納されています。一覧を下へスクロールして「SUBSTITUTE」をクリックします。
第一引数「文字列」
SUBSTITUTEの第一引数には置換を行う文字列が含まれる文字列全体を指定します。
多くの場合、置換を行う文字列が含まれるセルを参照します。
第二引数「検索文字列」
SUBSTITUTEの第二引数には置換する元の文字列を指定します。
この例では「“Excel”」と半角ダブルクォーテーションで括って入力しました。
（ダイアログで指定するなら、Excelと入力してTabキーを押せば自動でダブルクォーテーションが付きます）
第三引数「置換文字列」
SUBSTITUTEの第三引数には置換したい文字列を指定します。
この例では「“エクセル”」と半角ダブルクォーテーションで括って入力しました。
（ダイアログで指定するなら、エクセルと入力してTabキーを押せば自動でダブルクォーテーションが付きます）
第四引数「置換対象」
SUBSTITUTEの第四引数には置換する文字列の位置情報を指定します。省略した場合は指定文字列内の全ての検索文字列が置換文字列に置き換わります。
「1」と指定すれば最初に出現する検索文字列だけが置換されます。
ここでは「2」と指定したので、一番最初に出現する検索文字列はそのまま、２番めに出現する検索文字列だけが置換されます。検索文字列が一つしかなければ置換は行われません。
SUBSTITUTEの計算式と結果
SUBSTITUTE関数の引数の指定はこれで完了です。
計算式はこうなります。 =SUBSTITUTE(A2,”Excel”,”エクセル”,2)
この式を範囲にフィルコピーすると、SUBSTITUTEで文字列が正常に置換されました。
２番めのExcelがエクセルに置き換わっています。
スピルが使えるExcel（2019～、365）でのsubstituteの計算式
スピル機能が使えるExcel環境では第一引数「文字列」を「A2:A6」と範囲指定します。
計算式 =SUBSTITUTE(A2:A6,”Excel”,”エクセル”,2)
この式を最初のセルに入れるだけで、他のセルへ予測値が自動表示されます。オートフィルコピーする必要はありません。
SUBSTITUTEで複数の文字列を一括で置換する
複数の文字列をまとめて一括で置換したいなら、SUBSTITUTE関数をSUBSTITUTE関数にネストして実装します。
下図の文字列からExcelとWordの２つの文字列をカタカナに置換したいと思います。
複数の文字列の１つ目を置換するsubstituteの計算式を作る
まず、複数の文字列の１つ目を置き換える計算式をSUBSTITUTE関数で組み立てますが、ここでは前章で作った計算式「SUBSTITUTE(A2,”Excel”,”エクセル”,2)」をそのまま流用します。
この計算式をSUBSTITUTE関数に入れ子して、複数の文字列の一括置換を実装します。
SUBSTITUTE関数にSUBSTITUTE関数を入れ子する
Excelをエクセルに置き換える計算式をSUBSTITUTE関数の第一引数「文字列」に入力します。
「検索文字列」には”Word”、置換文字列には「”ワード”」を指定、「置換対象」に2を指定します。
最終的には =SUBSTITUTE(SUBSTITUTE(A2,”Excel”,”エクセル”,2),“Word”,“ワード”,2) となります。
「2番めに出現するExcelをエクセルに置換した文字列の中から2番めに出現するWordを検索して、あればワードに置き換える」という計算式です。
この式を範囲にフィルコピーすると、SUBSTITUTEで複数の文字列が一括で置換されました。
これもスピルが使えるExcel（2019～、365）では、この式を最初のセルに入れるだけで、他のセルへ予測値が自動表示されます。
=SUBSTITUTE(SUBSTITUTE(A2:A6,”Excel”,”エクセル”,2),”Word”,”ワード”,2) オートフィルコピーは不要です。
SUBSTITUTEで特定の文字列を削除する
会社形態表記や空白スーペスなど、複数の不要な文字列をまとめて一括で削除したい場合もSUBSTITUTE関数をネストして実装します。
下図の社名から、「（株）」及び「（有）」を一括で削除してみましょう。
複数の文字列の１つ目を削除するsubstituteの計算式を作る
まず、複数の文字列の１つ目を削除する計算式をSUBSTITUTE関数で組み立てます。
ここでは「（株）」を削除する式にしましたが、（有）の方でもOK。結果は同じです。
この計算式をSUBSTITUTE関数に入れ子して、複数の文字列の一括削除を実装します。
SUBSTITUTE(B2,”（株）”,””)
これは「B2セルに（株）があれば空白にする」という計算式で、置換文字列の「””」は空白を示しています。
この（株）の削除の計算式をSUBSTITUTE関数の第一引数「文字列」に入力します。
第二引数「検索文字列」には”（有）”を指定、置換文字列には「””」を指定します。
最終的には =SUBSTITUTE(SUBSTITUTE(B2,”（株）”,””),“（有）”,“”) となります。
「（株）を削除した文字列の中から（有）を検索して、あれば空白にする」という計算式です。
この式を範囲にフィルコピーすると、SUBSTITUTEで複数の特定の文字列が削除されました。
半角スペース・全角スペース（空白）の削除
文字列を削除するのに空白に置き換えましたが、空白自体を削除・置換するにはどうしたらいいでしょう？
これは半角スペースまたは全角スペースをダブルクォーテーションで括って指定します。
半角スペースと全角スペースは区別されますから、削除・置換する空白がどちらなのかをよく確認しましょう。
全角スペース「“ ”」 半角スペース「” “」
置換文字列に入れて削除に使われる空白はダブルクォーテーションを何も挟まずに続けて入力します。「“”」
改行の削除
改行に対応するコード番号をCHAR関数で呼び出し、それをSUBSTITUTEの「検索文字列」に入力します。
改行が削除されて文字列が１行になりました。
- SUBSTITUTE（サブスティテュート・略してサブ）は文字列を他の文字列に置換する関数です
- SUBSTITUTEの第一引数は「文字列」。置換を行う対象の文字列を含む文字列全体を、多くはセル参照で指定します
- SUBSTITUTEの第二引数は「検索文字列」。どの文字列を置換したいかを指定します
- SUBSTITUTEの第三引数は「置換文字列」。置き換えたい文字列を指定します
- SUBSTITUTEの第四引数は「置換対象」。置換する元の文字列の位置情報を数値で指定します
- SUBSTITUTEで複数の文字列をまとめて一括で置換したいなら、SUBSTITUTE関数をSUBSTITUTE関数にネストして実装します
- SUBSTITUTEで特定の文字列（会社形態表記や空白スーペスなど）をまとめて削除したい場合もSUBSTITUTE関数をネストして実装します
- 全角スペースを検索文字列に指定するときは“ ”、半角スペースは” “
- 改行を検索文字列に指定するときは文字コードをCHAR関数で呼び出します。CHAR(10)