特定の文字列を別の文字列に置換するSUBSTITUTE関数［Excel関数］

新年度の組織改編や担当者の変更の際、組織名や担当者名を一括で変更したい場合がありますよね。

このような場合に、Excelでは、文字列を置換する関数として、SUBSTITUTE（サブスティチュート）関数とREPLACE（リプレイス）関数を利用できます。

この記事では、特定の文字列を別の文字列に置き換える（入れ替える）SUBSTITUTE関数の基本的な使い方から関数を組み合わせる実践的な使い方までを紹介します。

SUBSTITUTE関数は、特定の文字列を別の文字列に置き換える関数で、Excel機能の［置換］（［Ctrl］＋［H］）とそっくりな部分がある一方で、この関数にしかできないことがありますので、丁寧に解説します。

SUBSTITUTE関数の書式

SUBSTITUTE（サブスティチュート）関数の引数（ひきすう）は、文字列、検索文字列、置換文字列、置換対象の4つです。

何番目の文字列を置き換えるかを指定できる、第4引数の置換対象は省略することができます。

SUBSTITUTE( 文字列,検索文字列,置換文字列[,置換対象] )

文字列	置換対象の文字列が含まれるセルを指定する。
検索文字列	置き換える元の文字列を指定する。
置換文字列	置き換え後の文字列を指定する。
置換対象	何番目に出てくる（検索）文字列を置き換えるかを数値で指定する。省略した場合には、すべての（検索）文字列が置き換えられる。

SUBSTITUTE関数の基本的な使い方

文字列の一部を置き換える

SUBSTITUTE関数の基本的な使い方を、例を用いて紹介します。

いま、B2セル～B4セルに入力されている「部」を「戦略部」に置き換えたいとします。このときは、C2セルに、=SUBSTITUTE(B2,"部","戦略部")と入力し、C3セル、C4セルに式をコピーします。

※第2引数の「検索文字列」や第3引数の「置換文字列」を、セル参照ではなく式の中で指定するときは""（ダブルクォーテーション）で囲むことを忘れないようにしてください。

1番目に現れる文字列だけを置換する

SUBSTITUTE関数の第4引数［置換対象］に1を指定すると、1番目に現れる検索文字列だけを置き換えることができます。

1つ目の「総務」だけを「戦略」に置き換える

B2セル～B4セルに入力されている1つ目の「総務」を「戦略」に置き換えるには、C2セルに、=SUBSTITUTE(B2,"総務","戦略",1)と入力し、C3セル、C4セルに式をコピーします。

1つ目の「A」だけを「M」に置き換える

B2セル～B4セルに入力されている1つ目の「A」を「M」に置き換えるには、C2セルに、=SUBSTITUTE(B2,"A","M",1)と入力し、C3セル、C4セルに式をコピーします。

2番目に現れる文字列だけを置換する

SUBSTITUTE関数の第4引数［置換対象］に2を指定すると、2番目に現れる検索文字列だけを置き換えることができます。

2つ目の「総務」だけを「企画」に置き換える

B2セル～B4セルに入力されている2つ目の「総務」を「企画」に置き換えるには、C2セルに、=SUBSTITUTE(B2,"総務","企画",2)と入力し、C3セル、C4セルに式をコピーします。

2つ目の「A」だけを「M」に置き換える

B2セル～B4セルに入力されている2つ目の「A」を「M」に置き換えるには、C2セルに、=SUBSTITUTE(B2,"A","M",2)と入力し、C3セル、C4セルに式をコピーします。

ネスト（入れ子）にして2つ以上の文字列を置換

SUBSTITUTE関数の中に、SUBSTITUTE関数を入れる形「ネスト」にすると、2つの文字列を同時に置換できます。

例えば、 =SUBSTITUTE(SUBSTITUTE(B2,"A","M"),"B","P") というような形にすると、「 A → M 」、「 B → P 」に同時に置き換えることができます。

さらにSUBSTITUTE関数をネストにすれば、3つの文字列を同時に置換することができます。

特定の記号だけを数える

セルに入力された文字列の中から特定の記号（たとえば、「〇」）の個数を数えたいときには、全体の文字数から数えたい記号以外の文字数を引き算します。

特定の記号を、SUBSTITUTE関数で空白に置き換え、全体の文字数から置き換え後の文字数を引くという手順です。

以下では、「○」の数を求めてみます。

LEN関数で全体の文字数を求める

まずは、LEN関数で全体の文字数を数えます。C2セルに、 =LEN(B2) と入力し、B2セルの文字数を求めます。ここでは、「○」と「×」が合わせて10個ありますので、「10」が返されます。

SUBSTITUTE関数で記号を空白に置き換える

次は、SUBSTITUTE関数で特定の記号（ここでは、「○」）を空白に置き換えます。C2セルに、続けて、 SUBSTITUTE(B2,"○","") と入力します。

置き換え後の文字数を全体文字数から引く

SUBSTITUTE関数で「○」を空白に置き換えた後の文字数を、LEN関数で求め、全体の文字数から引きます。

C2セルに、=LEN(B2)-LEN(SUBSTITUTE(B2,"○","")) という式を入力し、C7セルまでコピーすれば完成です。

改行を削除する

セルに入力された複数行のデータを1行で表示するには、CHAR関数とSUBSTITUTE関数を使って改行を削除します。ただし、改行を削除したときにデータがつながって見にくくなる場合には、SUBSTITUTE関数を使って改行をスペースに置き換えます。

SUBSTITUTE関数で改行を削除する

SUBSTITUTE関数で［改行］を削除するには、第2引数の「検索文字列」に、［改行］を指定する必要があります。［改行］は画面に表示されない制御文字なので、［数値］で指定した文字コードに対応する文字を返す関数、CHAR関数を使用して指定します。

CHAR関数の［改行］の文字コードは「10」ですので、引数に、「10」を指定します。B2セルには、=SUBSTITUTE(A2,CHAR(10),"") という式を入力します。

SUBSTITUTE関数で改行をスペースに置換する

単に、SUBSTITUTE関数で［改行］を削除しただけでは、データがつながってしまって見にくくなるという場合には、SUBSTITUTE関数を使って［改行］をスペースに置き換えます。B2セルには、=SUBSTITUTE(A2,CHAR(10),"　") という式を入力します。

まとめ

Excelで文字列をまとめて修正するときには、「置換」（［Ctrl］＋［H］）機能をよく使います。

ですが、元の文字列を残しておきたいときや、文字列の中で置換したい文字と置換したくない文字が混在するとき、置換したい文字列が複数あるときには、SUBSTITUTE関数を使って置換する方が効率的です。

状況に応じて使い分けることができると強いです。

最後までお読みいただきありがとうございました。