Excel(エクセル）で文字を別の文字に置き換えたい時は、『置換』機能が便利です。
ただ、置換機能を使うと、置き換える前の文字が消えてしまいます。
そんな時は、エクセルのSUBSTITUTE関数(サブスティテュート)を利用すると便利です。
この記事では、SUBSTITUTE関数で文字を置き換える方法と、複数の文字を同時にまとめて置き換える方法を紹介します。
記事の最後には、『置換』機能とSUBSTITUTE関数の違い、それぞれのメリットを紹介します。
SUBSTITUTE関数とは？
SUBSTITUTE関数の読み方は、サブスティチュート関数と読みます。
SUBSTITUTEは英語で、日本語では『代わり』『代替』『交換』といった意味を持ちます。
その意味からもイメージできるように、SUBSTITUTE関数とは、セル内の文字を別の文字に置き換えることができる関数です。
SUBSTITUTE関数の構文と引数
SUBSTITUTE関数の構文と引数は、以下のとおりです。
＝SUBSTITUTE(文字列,検索文字列,置換文字列,[置換対象])
それぞれの引数の詳細は、以下のとおりです。
第１引数「文字列」：置き換えたい文字が入力されているセルを指定します。セルを指定せず、文字列を指定する場合は「”」で囲みます。（例：A2、B4）
第２引数「検索文字列」： どの文字を置換するか、セルもしくは文字列を指定します。セルを指定せず、文字列を指定する場合は「”」で囲みます。（例：”MS”、B4）
第３引数「置換文字列」： どの文字に置換するか、セルもしくは文字列を指定します。セルを指定せず、文字列を指定する場合は「”」で囲みます。（例：”ON”、C4）
第４引数「[置換対象]」：省略可能です。 何番目の「検索文字列」を置換するかを数字で指定します。 数字を指定した場合、指定した順番の文字列だけが置き換えられます。 （例：１、２）
⇩指定しない場合
検索文字列中のすべての文字列が置換文字に置き換えられます。
SUBSTITUTE関数の使い方
例えば、以下のように、SUBSTITUTE関数を利用することで、文字列に含まれる句読点の『、』を、すべて『。』に置き換えすることが出来ます。
=SUBSTITUTE(B3,"、","。")
仮に、２つ目の「、」だけを「。」に修正したい場合は、４つ目の引数「[置換対象]」に”２”と入力します。
=SUBSTITUTE(B3,"、","。",2)
SUBSTITUTE関数で文字を置き換える方法
それでは、SUBSTITUTE関数を使って、文字を置き換える方法を紹介します。
実例として、下の表の商品コード「DK」をすべて「DR」に置き換えします。
手順１．SUBSTITUTE関数を挿入
D列に新しい商品コードを表示させたいので、D3セルにSUBSTITUTE関数を挿入します。
セルに「=SU」と入力すると、利用する関数の候補が表示されます。
その中からSUBSTITUTE関数を選択し、 「Tab 」キーを押します。(ダブルクリックで選択も可）
そうすると、SUBSTITUTE関数を挿入することができます。
=SUBSTITUTE(
手順２．SUBSTITUTE関数の第１引数「文字列」を指定
次に置き換えたい文字列が入っているC3セルを選択します。
C3セルをクリックしたら、次の引数を選択するため「,」(カンマ)を入力します。
=SUBSTITUTE(C3,
手順３．第２引数「検索文字列」を指定
次に、置き換えたい文字列を指定します。
今回は「DK」という文字を「DR」という文字に置換したいので、「DK」を指定します。
文字列はダブルクオーテーション「””」で囲む必要があるので、「“DK”」と入力します。入力したら、次の引数を選択するため「,」(カンマ)を入力します。
=SUBSTITUTE(C3,"DK",
「DK」という文字はG3セルにも入力されているので、以下のように、セル番号を指定することもできます。
その際は、他のセルにも関数を利用できるようにするために、絶対参照「$」の設定を忘れないようにしてください。
=SUBSTITUTE(C3,$G$3,
絶対参照が分からない方は、以下の記事を参考にしてください。
手順４．SUBSTITUTE関数の第３引数「置換文字列」を指定
次に、置き換え後の文字列を指定します。
今回は「DK」という文字を「DR」という文字に置換したいので、「DR」を指定します。
文字列はダブルクオーテーション「””」で囲む必要があるので、「“DR”」と入力します。入力したら、「）」(括弧)を入力します。
=SUBSTITUTE(C3,"DK","DR")
「DR」という文字はH3セルにも入力されているので、以下のように、セル番号を指定することもできます。
その際は、他のセルにも関数を利用できるようにするために、絶対参照「$」の設定を忘れないようにしてください。
=SUBSTITUTE(C3,$G$3,$H$3)
手順５．文字列の置き換えが完成
「Enter」キーを押して、SUBSTITUTE関数を確定させてください。
そうすると、「DK」が「DR」という文字に置き換えさえれました。
最後に、D3セルの下のセルにもSUBSTITUTE関数を使うために、D3セルの右下角にある十字マークをダブルクリックして、オートフィルでSUBSTITUTE関数をコピーします。
SUBSTITUTE関数で複数の文字を置き換える方法
SUBSTITUTE関数の中に、SUBSTITUTE関数を挿入することで、一度に複数の文字を置き換えすることができます。
下の表の商品コードの「DK」を「DR」に、さらに同時に「F」を「FD」に一度に置き換えする方法を紹介します。
手順１．SUBSTITUTE関数の第１引数にSUBSTITUTE関数を挿入
D列に置き換え後の商品コードを表示させたいので、D3セルにSUBSTITUTE関数を挿入します。
次に、SUBSTITUTE関数の第１引数の中にSUBSTITUTE関数を挿入します。
関数の中に、関数を挿入することを「入れ子」もしくは「ネスト」といいますが、「入れ子」のテクニックを利用します。
２つ目のSUBSTITUTE関数で、「DK」が「DR」に置き換えされるよう、以下のように関数を設定します。入力したら、次の引数を選択するため「,」(カンマ)を入力します。
=SUBSTITUTE(SUBSTITUTE(C3,"DK","DR"),
手順２．SUBSTITUTE関数の第２引数を指定
１つ目のSUBSTITUTE関数の第２引数に、置き換えする文字列を指定します。
「F」を「FD」に置き換えしたいので、ダブルクォーテーションを使って、”F”と入力します。入力したら、次の引数を選択するため「,」(カンマ)を入力します。
=SUBSTITUTE(SUBSTITUTE(C3,"DK","DR"),"F",
手順３．SUBSTITUTE関数の第３引数を指定
１つ目のSUBSTITUTE関数の第３引数に、置き換え後の文字列を指定します。
「F」を「FD」に置き換えしたいので、ダブルクォーテーションを使って、”FD”と入力します。入力したら、SUBSTITUTE関数を閉じるため「）」(カッコ)を入力します。
=SUBSTITUTE(SUBSTITUTE(C3,"DK","DR"),"F","FD")
手順４．SUBSTITUTE関数をオートフィルでコピー
「Enter」キーを押して、SUBSTITUTE関数を確定させてください。
最後に、D3セルの下のセルにもSUBSTITUTE関数を使うために、D3セルの右下角にある十字マークをダブルクリックして、オートフィルでSUBSTITUTE関数をコピーします。
そうすると、「DK」を「DR」に、「F」を「FD」に、２つの文字列を同時に置き換えすることができました。
上の例では、SUBSTITUTE関数の引数はダブルクォーテーション””を利用して文字列を指定しましたが、下のように、セルを指定して作成することもできます。
=SUBSTITUTE(SUBSTITUTE(C3,$G$3,$H$3),$G$4,$H$4)
SUBSTITUTE関数と「置換」機能との違いを紹介
SUBSTITUTE関数の使い方を紹介してきましたが、エクセルの「置換」機能と何が違うのでしょうか。
文字を置き換えるという意味で機能は同じように思えますが、SUBSTITUTE関数、「置換」機能、それぞれの良さがあります。
最後に、SUBSTITUTE関数と「置換」機能の違いを紹介しますので、どういったケースに利用すれば便利なのかを判断する材料にしてください。
SUBSTITUTE関数は、置換前のデータが残る
SUBSTITUTE関数は、置換したい文字を指定して、その結果を別のセルに表示するので、置換前のデータが残ります。
一方、置換機能は、置換が行われると、置換前のデータは残りません。
置換前の記録を残しておきたい、もしくは置換する文字を変更したい場合は、SUBSTITUTE関数がお奨めです。
「置換」機能は、数式の置換が可能
「置換」機能では、文字列だけでなく数式も置換することが出来ます。
例えば、下の表のように、７行目のSUM関数が『=SUM(E3:E5)』となっていて、６行目の数値が合計されていません。
このように、複数のセルのSUM関数のセル範囲が誤っている場合には、置換機能を利用すると便利です。
SUM関数が挿入されているセル範囲を選択してから、置換機能を使って、検索する文字列を「5」、置換後の文字列を「6」と設定します。
そうすると、７行目のSUM関数の数式をすべて修正することができました。
修正された数式
『=SUM(C3:C5)』⇒『=SUM(C3:C6)』
『=SUM(D3:D5)』⇒『=SUM(D3:D6)』
『=SUM(E3:E5)』⇒『=SUM(E3:E6)』
SUBSTITUTE関数は、一度に複数の置換が可能
今回紹介したように、SUBSTITUTE関数は複数の文字を同時に置き換えすることが出来ます。
一方、「置換」機能であれば、置換作業を複数回実行する必要があります。
SUBSTITUTE関数は、何番目の文字を置換するか指定できる
SUBSTITUTE関数の場合、第４引数「[置換対象]」を利用することで、一部の文字だけを置換することが出来ます。
例えば、下のようにSUBSTITUTE関数を挿入することで、文字列の中の２番目の「-」を削除(文字なしに置換)することが出来ます。
=SUBSTITUTE(C3,"-","",2)
「置換」機能は、大文字、小文字、半角、全角を区別できる
「置換」機能は、大文字、小文字、半角、全角の区別する/しないを選択することができます。
例えば、大文字「A」を「B」に置換したいが、小文字「a」を「B」に置換したくない、といった選択が可能です。
一方、SUBSTITUTE関数に区別する/しないを選択することは出来ません。
