SUBSTITUTE関数の使い方|複数の文字列の置き換えもできる

Excel(エクセル)SUBSTITUTE関数の使い方|複数の文字列の置き換えもできる

使用関数:SUBSTITUTE

SUBSTITUTEとは?

SUBSTITUTEの読み方と意味

SUBSTITUTEは、サブスティテュートと読みます。
交代要員や代替のことを「サブ」と言いますが、これはSUBSTITUTEの略です。

SUBSTITUTEは置換を意味し、 その意味通り、特定の文字列を他の文字列に置き換える関数です。

REPLACEとの違い

文字列を置換する関数にはSUBSTITUTEの他にもう一つREPLACEがあります。

検索文字列の指定の違い

REPLACE関数が置き換える位置と置き換える文字数で検索文字列を指定するのに対して、SUBSTITUTEは引数「検索文字列」でストレートに指定します。以下はREPLACEとSUBSTITUTEの数式の比較です。

REPLACEとSUBSTITUTEの数式で検索文字列の比較

複数の置換への対応の違い

元の文字列に複数の検索文字列が含まれていた場合、REPLACE関数は位置と文字数の指定でどれか一つの検索文字列に対して置換を実行します。言い換えると、複数の文字列を一括で置換することはできません。

SUBSTITUTE関数は元の文字列に含まれる全ての検索文字列をまとめて置き換えすることが可能です。
SUBSTITUTEで複数の文字列を一括で置換

REPLACE関数の使い方はこちらで詳しく解説しています

REPLACE関数の使い方|文字列の置換、特定文字の挿入
REPLACE関数の使い方を分かりやすく図解。REPLACE関数とSUBSUTITUTE関数の違い。REPLACE関数の引数「文字列・開始位置・文字数・置換文字列」の指定、「文字数」に0を指定して特定の文字を開始位置の前に入れる方法など

SUBSTITUTE関数の構成

SUBSTITUTEは「数式」タブ→「関数ライブラリ」→「文字列操作」に格納されています。一覧を下へスクロールして「SUBSTITUTE」をクリックします。
「数式」タブ→「関数ライブラリ」→「文字列操作」→SUBSTITUTE

第一引数「文字列」

SUBSTITUTEの第一引数には置換を行う文字列が含まれる文字列全体を指定します。
多くの場合、置換を行う文字列が含まれるセルを参照します。
SUBSTITUTEの第一引数「文字列」の指定方法

第二引数「検索文字列」

SUBSTITUTEの第二引数には置換する元の文字列を指定します。
この例では「“Excel”」と半角ダブルクォーテーションで括って入力しました。
(ダイアログで指定するなら、Excelと入力してTabキーを押せば自動でダブルクォーテーションが付きます)
SUBSTITUTEの第二引数「検索文字列」の指定方法

第三引数「置換文字列」

SUBSTITUTEの第三引数には置換したい文字列を指定します。

この例では「“エクセル”」と半角ダブルクォーテーションで括って入力しました。
(ダイアログで指定するなら、エクセルと入力してTabキーを押せば自動でダブルクォーテーションが付きます)
SUBSTITUTEの第三引数「置換文字列」の指定方法

第四引数「置換対象」

SUBSTITUTEの第四引数には置換する文字列の位置情報を指定します。省略した場合は指定文字列内の全ての検索文字列が置換文字列に置き換わります。
「1」と指定すれば最初に出現する検索文字列だけが置換されます。

ここでは「2」と指定したので、一番最初に出現する検索文字列はそのまま、2番めに出現する検索文字列だけが置換されます。検索文字列が一つしかなければ置換は行われません。
SUBSTITUTEの第四引数「置換対象」の指定方法

SUBSTITUTEの計算式と結果

SUBSTITUTE関数の引数の指定はこれで完了です。
SUBSTITUTE関数の引数の指定

計算式はこうなります。 =SUBSTITUTE(A2,”Excel”,”エクセル”,2)

この式を範囲にフィルコピーすると、SUBSTITUTEで文字列が正常に置換されました。
SUBSTITUTEで文字列が正常に置換
2番めのExcelがエクセルに置き換わっています。

スピルが使えるExcel(2019~、365)でのsubstituteの計算式

スピル機能が使えるExcel環境では第一引数「文字列」を「A2:A6」と範囲指定します。
スピル機能が使えるExcel環境では第一引数「文字列」を「A2:A6」と範囲指定

計算式 =SUBSTITUTE(A2:A6,”Excel”,”エクセル”,2)
この式を最初のセルに入れるだけで、他のセルへ予測値が自動表示されます。オートフィルコピーする必要はありません。

SUBSTITUTEで複数の文字列を一括で置換する

複数の文字列をまとめて一括で置換したいなら、SUBSTITUTE関数をSUBSTITUTE関数にネストして実装します。

下図の文字列からExcelとWordの2つの文字列をカタカナに置換したいと思います。
複数の文字列を一括で置換する表

複数の文字列の1つ目を置換するsubstituteの計算式を作る

まず、複数の文字列の1つ目を置き換える計算式をSUBSTITUTE関数で組み立てますが、ここでは前章で作った計算式「SUBSTITUTE(A2,”Excel”,”エクセル”,2)」をそのまま流用します。
この計算式をSUBSTITUTE関数に入れ子して、複数の文字列の一括置換を実装します。

SUBSTITUTE関数にSUBSTITUTE関数を入れ子する

Excelをエクセルに置き換える計算式をSUBSTITUTE関数の第一引数「文字列」に入力します。
「検索文字列」には”Word”、置換文字列には「”ワード”」を指定、「置換対象」に2を指定します。
複数の文字列の置換でSUBSTITUTE関数を入れ子したSUBSTITUTE関数の引数ダイアログ

最終的には =SUBSTITUTE(SUBSTITUTE(A2,”Excel”,”エクセル”,2),“Word”,“ワード”,2) となります。
2番めに出現するExcelをエクセルに置換した文字列の中から2番めに出現するWordを検索して、あればワードに置き換える」という計算式です。

この式を範囲にフィルコピーすると、SUBSTITUTEで複数の文字列が一括で置換されました。
SUBSTITUTEで複数の文字列が同時に置換された

これもスピルが使えるExcel(2019~、365)では、この式を最初のセルに入れるだけで、他のセルへ予測値が自動表示されます。
=SUBSTITUTE(SUBSTITUTE(A2:A6,”Excel”,”エクセル”,2),”Word”,”ワード”,2) オートフィルコピーは不要です。

SUBSTITUTEで特定の文字列を削除する

会社形態表記や空白スーペスなど、複数の不要な文字列をまとめて一括で削除したい場合もSUBSTITUTE関数をネストして実装します。

下図の社名から、「(株)」及び「(有)」を一括で削除してみましょう。
SUBSTITUTEで(株)(有)を一括で削除する表

複数の文字列の1つ目を削除するsubstituteの計算式を作る

まず、複数の文字列の1つ目を削除する計算式をSUBSTITUTE関数で組み立てます。
ここでは「(株)」を削除する式にしましたが、(有)の方でもOK。結果は同じです。
この計算式をSUBSTITUTE関数に入れ子して、複数の文字列の一括削除を実装します。
複数の文字列の1つ目を削除するSUBSTITUTE関数の引数ダイアログ

SUBSTITUTE(B2,”(株)”,””)
これは「B2セルに(株)があれば空白にする」という計算式で、置換文字列の「””」は空白を示しています。

この(株)の削除の計算式をSUBSTITUTE関数の第一引数「文字列」に入力します。
第二引数「検索文字列」には”(有)”を指定、置換文字列には「””」を指定します。
特定の文字列の削除でSUBSTITUTE関数を入れ子したSUBSTITUTE関数の引数ダイアログ

最終的には =SUBSTITUTE(SUBSTITUTE(B2,”(株)”,””),“(有)”,“”) となります。
(株)を削除した文字列の中から(有)を検索して、あれば空白にする」という計算式です。

この式を範囲にフィルコピーすると、SUBSTITUTEで複数の特定の文字列が削除されました。
SUBSTITUTEで複数の特定の文字列が削除された

半角スペース・全角スペース(空白)の削除

文字列を削除するのに空白に置き換えましたが、空白自体を削除・置換するにはどうしたらいいでしょう?

これは半角スペースまたは全角スペースをダブルクォーテーションで括って指定します。
半角スペースまたは全角スペースをダブルクォーテーションで括って指定

半角スペースと全角スペースは区別されますから、削除・置換する空白がどちらなのかをよく確認しましょう。
全角スペース「“ ”」 半角スペース「” “

置換文字列に入れて削除に使われる空白はダブルクォーテーションを何も挟まずに続けて入力します。「“”

改行の削除

改行に対応するコード番号をCHAR関数で呼び出し、それをSUBSTITUTEの「検索文字列」に入力します。
SUBSTITUTE関数の「検索文字列」にCHAR関数を入れ子

改行が削除されて文字列が1行になりました。
SUBSTITUTEで改行が削除された文字列

まとめ
  • SUBSTITUTE(サブスティテュート・略してサブ)は文字列を他の文字列に置換する関数です
  • SUBSTITUTEの第一引数は「文字列」。置換を行う対象の文字列を含む文字列全体を、多くはセル参照で指定します
  • SUBSTITUTEの第二引数は「検索文字列」。どの文字列を置換したいかを指定します
  • SUBSTITUTEの第三引数は「置換文字列」。置き換えたい文字列を指定します
  • SUBSTITUTEの第四引数は「置換対象」。置換する元の文字列の位置情報を数値で指定します
  • SUBSTITUTEで複数の文字列をまとめて一括で置換したいなら、SUBSTITUTE関数をSUBSTITUTE関数にネストして実装します
  • SUBSTITUTEで特定の文字列(会社形態表記や空白スーペスなど)をまとめて削除したい場合もSUBSTITUTE関数をネストして実装します
  • 全角スペースを検索文字列に指定するときは“ ”、半角スペースは” “
  • 改行を検索文字列に指定するときは文字コードをCHAR関数で呼び出します。CHAR(10)
タイトルとURLをコピーしました