【Excel/エクセル】複数の文字を置き換えるSUBSTITUTE関数の使い方を紹介

エクセルのSUBSTITUTE関数で文字を別の文字に置き換える方法

Excel(エクセル)で文字を別の文字に置き換えたい時は、『置換』機能が便利です。

ただ、置換機能を使うと、置き換える前の文字が消えてしまいます。

そんな時は、エクセルのSUBSTITUTE関数(サブスティテュート)を利用すると便利です。

この記事では、SUBSTITUTE関数で文字を置き換える方法と、複数の文字を同時にまとめて置き換える方法を紹介します。

記事の最後には、『置換』機能とSUBSTITUTE関数の違い、それぞれのメリットを紹介します。

SUBSTITUTE関数とは?

SUBSTITUTE関数の読み方は、サブスティチュート関数と読みます。

SUBSTITUTEは英語で、日本語では『代わり』『代替』『交換』といった意味を持ちます。

その意味からもイメージできるように、SUBSTITUTE関数とは、セル内の文字を別の文字に置き換えることができる関数です。

SUBSTITUTE関数の構文と引数

SUBSTITUTE関数の構文と引数は、以下のとおりです。

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

それぞれの引数の詳細は、以下のとおりです。

第1引数文字列」:置き換えたい文字が入力されているセルを指定します。セルを指定せず、文字列を指定する場合は「”」で囲みます。(例:A2、B4)

第2引数検索文字列」: どの文字を置換するか、セルもしくは文字列を指定します。セルを指定せず、文字列を指定する場合は「”」で囲みます。(例:”MS”、B4)

第3引数置換文字列」: どの文字に置換するか、セルもしくは文字列を指定します。セルを指定せず、文字列を指定する場合は「”」で囲みます。(例:”ON”、C4)

第4引数[置換対象]」:省略可能です。 何番目の「検索文字列」を置換するかを数字で指定します。 数字を指定した場合、指定した順番の文字列だけが置き換えられます。 (例:1、2)
⇩指定しない場合
検索文字列中のすべての文字列が置換文字に置き換えられます。

SUBSTITUTE関数の使い方

例えば、以下のように、SUBSTITUTE関数を利用することで、文字列に含まれる句読点の『、』を、すべて『。』に置き換えすることが出来ます。

=SUBSTITUTE(B3,"","")

エクセルのSUBSTITUTE関数の使い方(すべて置換)
エクセルのSUBSTITUTE関数の使い方(すべて置換)

仮に、2つ目の「、」だけを「。」に修正したい場合は、4つ目の引数[置換対象]」に”2”と入力します。

=SUBSTITUTE(B3,"、","。",2)

エクセルのSUBSTITUTE関数の使い方(一部だけを置換)
エクセルのSUBSTITUTE関数の使い方(一部だけを置換)

SUBSTITUTE関数で文字を置き換える方法

それでは、SUBSTITUTE関数を使って、文字を置き換える方法を紹介します。

実例として、下の表の商品コード「DK」をすべて「DR」に置き換えします。

SUBSTITUTE関数を使って、商品コードを置き換え
SUBSTITUTE関数を使って、商品コードを置き換え

手順1.SUBSTITUTE関数を挿入

D列に新しい商品コードを表示させたいので、D3セルにSUBSTITUTE関数を挿入します。

セルに=SU」と入力すると、利用する関数の候補が表示されます。

その中からSUBSTITUTE関数を選択し、 「Tab 」キーを押します。(ダブルクリックで選択も可)

関数候補からSUBSTITUTE関数を選択
関数候補からSUBSTITUTE関数を選択

そうすると、SUBSTITUTE関数を挿入することができます。

=SUBSTITUTE(

SUBSTITUTE関数を挿入
SUBSTITUTE関数を挿入

手順2.SUBSTITUTE関数の第1引数「文字列」を指定

次に置き換えたい文字列が入っているC3セルを選択します。

C3セルをクリックしたら、次の引数を選択するため「,」(カンマ)を入力します。

=SUBSTITUTE(C3,

SUBSTITUTE関数の第1引数「文字列」を選択
SUBSTITUTE関数の第1引数「文字列」を選択

手順3.第2引数「検索文字列」を指定

次に、置き換えたい文字列を指定します。

今回は「DK」という文字を「DR」という文字に置換したいので、「DK」を指定します。

文字列はダブルクオーテーション「””」で囲む必要があるので、「“DK”」と入力します。入力したら、次の引数を選択するため「,」(カンマ)を入力します。

=SUBSTITUTE(C3,"DK",

SUBSTITUTE関数の第2引数「検索文字列」を指定
SUBSTITUTE関数の第2引数「検索文字列」を指定

「DK」という文字はG3セルにも入力されているので、以下のように、セル番号を指定することもできます

その際は、他のセルにも関数を利用できるようにするために、絶対参照「$」の設定を忘れないようにしてください。

=SUBSTITUTE(C3,$G$3,

SUBSTITUTE関数の第2引数「検索文字列」をセルで指定
SUBSTITUTE関数の第2引数「検索文字列」をセルで指定

絶対参照が分からない方は、以下の記事を参考にしてください。

手順4.SUBSTITUTE関数の第3引数「置換文字列」を指定

次に、置き換え後の文字列を指定します。

今回は「DK」という文字を「DR」という文字に置換したいので、「DR」を指定します。

文字列はダブルクオーテーション「””」で囲む必要があるので、「“DR”」と入力します。入力したら、「」(括弧)を入力します。

=SUBSTITUTE(C3,"DK","DR")

SUBSTITUTE関数の第3引数「置換文字列」を指定
SUBSTITUTE関数の第3引数「置換文字列」を指定

「DR」という文字はH3セルにも入力されているので、以下のように、セル番号を指定することもできます

その際は、他のセルにも関数を利用できるようにするために、絶対参照「$」の設定を忘れないようにしてください。

=SUBSTITUTE(C3,$G$3,$H$3)

SUBSTITUTE関数の第3引数「置換文字列」をセルで指定
SUBSTITUTE関数の第3引数「置換文字列」をセルで指定

手順5.文字列の置き換えが完成

「Enter」キーを押して、SUBSTITUTE関数を確定させてください。

そうすると、「DK」が「DR」という文字に置き換えさえれました。

SUBSTITUTE関数で文字の置き換えが完了
SUBSTITUTE関数で文字の置き換えが完了

最後に、D3セルの下のセルにもSUBSTITUTE関数を使うために、D3セルの右下角にある十字マークをダブルクリックして、オートフィルでSUBSTITUTE関数をコピーします。

オートフィルでSUBSTITUTE関数をコピー
オートフィルでSUBSTITUTE関数をコピー

SUBSTITUTE関数で複数の文字を置き換える方法

SUBSTITUTE関数の中に、SUBSTITUTE関数を挿入することで、一度に複数の文字を置き換えすることができます。

下の表の商品コードの「DK」を「DR」に、さらに同時に「F」を「FD」に一度に置き換えする方法を紹介します。

SUBSTITUTE関数で複数の文字を一度に置き換えする方法
SUBSTITUTE関数で複数の文字を一度に置き換えする方法

手順1.SUBSTITUTE関数を挿入

D列に置き換え後の商品コードを表示させたいので、D3セルにSUBSTITUTE関数を挿入します。

セルに=SU」と入力すると、利用する関数の候補が表示されます。

その中からSUBSTITUTE関数を選択し、 「Tab 」キーを押します。(ダブルクリックで選択も可)

SUBSTITUTE関数を挿入
SUBSTITUTE関数を挿入

手順2.SUBSTITUTE関数の第1引数にSUBSTITUTE関数を挿入

次に、SUBSTITUTE関数の第1引数の中にSUBSTITUTE関数を挿入します。

関数の中に、関数を挿入することを「入れ子」もしくは「ネスト」といいますが、「入れ子」のテクニックを利用します。

2つ目のSUBSTITUTE関数で、「DK」が「DR」に置き換えされるよう、以下のように関数を設定します。入力したら、次の引数を選択するため「,」(カンマ)を入力します。

=SUBSTITUTE(SUBSTITUTE(C3,"DK","DR"),

SUBSTITUTE関数の中にSUBSTITUTE関数を挿入(入れ子を利用)
SUBSTITUTE関数の中にSUBSTITUTE関数を挿入(入れ子を利用)

手順3.SUBSTITUTE関数の第2引数を指定

1つ目のSUBSTITUTE関数の第2引数に、置き換えする文字列を指定します。

「F」を「FD」に置き換えしたいので、ダブルクォーテーションを使って、”F”と入力します。入力したら、次の引数を選択するため「,」(カンマ)を入力します。

=SUBSTITUTE(SUBSTITUTE(C3,"DK","DR"),"F",

1つ目のSUBSTITUTE関数の第2引数を設定
1つ目のSUBSTITUTE関数の第2引数を設定

手順4.SUBSTITUTE関数の第3引数を指定

1つ目のSUBSTITUTE関数の第3引数に、置き換え後の文字列を指定します。

「F」を「FD」に置き換えしたいので、ダブルクォーテーションを使って、”FD”と入力します。入力したら、SUBSTITUTE関数を閉じるため「」(カッコ)を入力します。

=SUBSTITUTE(SUBSTITUTE(C3,"DK","DR"),"F","FD")

1つ目のSUBSTITUTE関数の第3引数を設定
1つ目のSUBSTITUTE関数の第3引数を設定

手順5.SUBSTITUTE関数をオートフィルでコピー

「Enter」キーを押して、SUBSTITUTE関数を確定させてください。

最後に、D3セルの下のセルにもSUBSTITUTE関数を使うために、D3セルの右下角にある十字マークをダブルクリックして、オートフィルでSUBSTITUTE関数をコピーします。

SUBSTITUTE関数をオートフィルを使ってコピペ
SUBSTITUTE関数をオートフィルを使ってコピペ

そうすると、「DK」を「DR」に、「F」を「FD」に、2つの文字列を同時に置き換えすることができました。

SUBSTITUTE関数で複数の文字を同時に置き換えが完了
SUBSTITUTE関数で複数の文字を同時に置き換えが完了

上の例では、SUBSTITUTE関数の引数はダブルクォーテーション””を利用して文字列を指定しましたが、下のように、セルを指定して作成することもできます。

=SUBSTITUTE(SUBSTITUTE(C3,$G$3,$H$3),$G$4,$H$4)

SUBSTITUTE関数の引数にセル番号を指定することも可能
SUBSTITUTE関数の引数にセル番号を指定することも可能

SUBSTITUTE関数と「置換」機能との違いを紹介

SUBSTITUTE関数の使い方を紹介してきましたが、エクセルの「置換」機能と何が違うのでしょうか。

文字を置き換えるという意味で機能は同じように思えますが、SUBSTITUTE関数、「置換」機能、それぞれの良さがあります。

最後に、SUBSTITUTE関数と「置換」機能の違いを紹介しますので、どういったケースに利用すれば便利なのかを判断する材料にしてください。

SUBSTITUTE関数は、置換前のデータが残る

SUBSTITUTE関数は、置換したい文字を指定して、その結果を別のセルに表示するので、置換前のデータが残ります

一方、置換機能は、置換が行われると、置換前のデータは残りません。

置換前の記録を残しておきたい、もしくは置換する文字を変更したい場合は、SUBSTITUTE関数がお奨めです。

SUBSTITUTE関数のメリット:置換前のデータが残る
SUBSTITUTE関数のメリット:置換前のデータが残る

「置換」機能は、数式の置換が可能

「置換」機能では、文字列だけでなく数式も置換することが出来ます。

例えば、下の表のように、7行目のSUM関数が『=SUM(E3:E5)』となっていて、6行目の数値が合計されていません

置換機能は数式内の数字やアルファベットの置換が可能
置換機能は数式内の数字やアルファベットの置換が可能

このように、複数のセルのSUM関数のセル範囲が誤っている場合には、置換機能を利用すると便利です。

SUM関数が挿入されているセル範囲を選択してから、置換機能を使って、検索する文字列を「5」置換後の文字列を「6」と設定します。

置換機能を使って、SUM関数のセル範囲を修正
置換機能を使って、SUM関数のセル範囲を修正

そうすると、7行目のSUM関数の数式をすべて修正することができました。

置換機能によって、SUM関数の数式の修正が一度
置換機能によって、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関数の場合、第4引数「[置換対象]」を利用することで、一部の文字だけを置換することが出来ます。

例えば、下のようにSUBSTITUTE関数を挿入することで、文字列の中の2番目の「-」を削除(文字なしに置換)することが出来ます。

=SUBSTITUTE(C3,"-","",2)

SUBSTITUTE関数のメリット:一部のみ置換することが可能
SUBSTITUTE関数のメリット:一部のみ置換することが可能

「置換」機能は、大文字、小文字、半角、全角を区別できる

「置換」機能は、大文字、小文字、半角、全角の区別する/しないを選択することができます。

例えば、大文字「A」を「B」に置換したいが、小文字「a」を「B」に置換したくない、といった選択が可能です。

一方、SUBSTITUTE関数に区別する/しないを選択することは出来ません。

置換機能のメリット:大文字、小文字、半角、全角の区別が可能
置換機能のメリット:大文字、小文字、半角、全角の区別が可能