Microsoft Excel(エクセル）に入力されている文字列の一部を別の文字列に置き換えしたいことはないでしょうか。
例えば、特定の文字の間にスペースを挿入したり、半角スペースを全角スペースに変換したり、”－”(ハイフン)を削除するときです。
そんな文字を別の文字変換したり、特定の文字を削除するときに便利なのが、REPLACE関数とSUBSTITUTE関数とす。
REPLACE関数とSUBSTITUTE関数ともに文字を置換する関数ですが、置換対象を指定する方法が「文字」なのか「文字の位置」なのかで、使い方が異なります。
２つの関数の違い
・REPLACE関数は指定した位置の文字を変換
・SUBSTITUTE関数は、指定した文字を別の文字に変換
今回は、REPLACE関数の詳しい使い方を中心に、SUBSTITUTE関数の実例なども併せて紹介します。
SUBSTITUTE関数の使い方は、本記事以外にも、以下のリンク記事でも詳しく紹介しています。
REPLACE関数の使い方と事例
エクセルのREPLACE関数で、文字を置き換えすることが可能です。
REPLACEは「リプレイス」と読み、日本語で「置き換える」という意味の英語です。
RERLACE関数とは、文字列の中の指定した位置の文字列を指定した文字に置き換える関数です。
そのため、セルの先頭から３文字目から５文字目の文字だけを置き換えしたい、という場合に利用する関数です。
REPLACE関数の書式と引数
REPLACE関数の書式(構文)と引数は、以下のとおりです。
＝REPLACE(文字列, 開始位置, 文字数, 置換文字列)
第１引数「文字列」：文字を置き換えするセルを指定します。(例：B2)
第２引数「開始位置」：必ず指定します。 先頭から何文字目の文字から入れ替えたいか数字で指定します。先頭文字を入れ替える場合は「1」 になります。(例：3)
第３引数「文字数」：第２引数で指定した開始位置から何文字目までの文字を置き換えしたいか数字で指定します。２文字入れ替えたい場合は「２」とします。(例：２)
第４引数「置換文字列」：必ず指定します。 置き換えする文字列を指定します。関数の中で直接指定する場合は、ダブルクォーテーション(”)で囲みます。(例：B3、”C”)
REPLACE関数の使い方
それでは、実際にREPLACE関数(リプレイス)の使い方を紹介します。
今回は説明用に、B3セルの電話番号「090-9999-9999」の２桁目を”8”に変えて、「080-9999-9999」と置き換えします。
まず、「＝REPLACE(」と入力し、文字を変換したい対象のセルB3を選択します。選択したら、２つ目の引数を入力するために、カンマ(,)を続けてください。
=REPLACE(B3,
次に、REPLACE関数の第２引数「開始位置」を指定します。「090」の「9」は先頭から２番目に位置するので、「２」と入力します。
=REPLACE(B3,2,
次に、REPLACE関数の第３引数「文字数」を指定します。「090」の「9」の１文字だけを変更したいので、「１」と入力します。
=REPLACE(B3,2,1,
最後に、REPLACE関数の第4引数「置換文字列」を指定します。「9」を「8」に変更したいので、「8」と入力します。もし、数字ではなく文字に変更する場合は、”A”といったように、ダブルクォーテーションで囲います。
=REPLACE(B3,2,1,8)
数式を確定させると、下の図のように、B3セルの電話番号「090-9999-9999」が「080-9999-9999」と置き換えされます。
REPLACE関数の事例1(数値を”■”に置換)
REPLACE関数の事例を２つ紹介していきます。
まずは、電話番号の末尾４桁を見えないようにするために、「■」に置き換える方法です。４つ目の引数「置換文字列」は「”■■■■”」と入力します。
=REPLACE(B3,10,4,"■■■■")
そうすると、電話番号の末尾４桁が■に置き換えされます。
置換機能やSUBSTITUTE関数では置換前の文字を指定しないといけませんが、REPLACE関数は特定の位置の文字であればどんな文字でも置換するので、このような処理が可能となります。
REPLACE関数の事例2(スペースやハイフンを削除)
REPLACE関数を使って、電話番号や郵便番号の”-”(ハイフン)や、空白スペースを削除することができます。
REPLACE関数の４つ目の引数「置換文字列」を何も入力しないか、もしくは「””」と入力することで、指定した位置の文字を削除することができます。
=REPLACE(B3,4,1,"")
REPLACE関数の事例3(複数条件：２つの置換を実行)
REPLACE関数は、一度に複数の置換を行う複数条件での利用が可能です。
REPLACE関数の第１引数の中に、もう１つのREPLACE関数を挿入することで、２つの複数条件の置換を行うことができます。(入れ子の利用)
数式の構文は以下のとおりです。
=REPLACE(REPLACE(文字列１, 開始位置１, 文字数１, 置換文字列１), 開始位置２, 文字数２, 置換文字列２)
例えば、下の数式を用いることで、２つ目のREPLACE関数で電話番号の５桁目から８桁目を「▲」に、１つ目のREPLACE関数で９桁目から１２桁目を「■」に置き換えることができます。
=REPLACE(REPLACE(B3,5,4,"▲▲▲▲"),10,4,"■■■■")
３つの置換を同時に行いたい場合は、２つ目のREPLACE関数の中の第１引数の中に、３つ目のREPLACE関数を挿入します。
=REPLACE(REPLACE(REPLACE(文字列１, 開始位置１, 文字数１, 置換文字列１), 開始位置２, 文字数２, 置換文字列２), 開始位置３, 文字数３, 置換文字列３)
SUBSTITUTE関数の使い方と事例
続いて、エクセルのSUBSTITUTE関数を紹介します。
SUBSTITUTEの読み方はサブスティチュートで、日本語で「代わりの」を意味する英語です。
SUBSTITUTE関数とは、指定した文字を別の文字に置き換えすることができる関数です。
例えば、「A001」を「B001」に、「090」を「080」に、「A001」を「A-001」などに変更することができます。
さらに、SUBSTITUTE関数は「A」を「C」に、「B」を「D」に同時に置き換えるなど２つ以上の条件を設定することができます。
SUBSTITUTE関数の書式(構文)と引数は、以下のとおりです。
＝SUBSTITUTE(文字列,検索文字列,置換文字列,[置換対象])
第１引数「文字列」：文字を置き換えするセルを指定します。(例：B2)
第２引数「検索文字列」：文字列の中で置換したい部分に該当する文字列を指定します。文字列はダブルクォーテーション(”)で囲います。(例：”A”)
第３引数「置換文字列」：検索文字列に代わって置き換えしたい新しい文字列を指定します。文字列はダブルクォーテーション(”)で囲います。(例：”B”)
第４引数「置換対象」：入力は任意です。複数の検索文字列が見つかった場合、何番目の文字列を置き換えるかを数字で指定します。指定しない場合は、すべての検索文字列が置換されます。(例：１，２)
それでは、実際にSUBSTITUTE関数(サブスティチュート)の使い方を紹介します。
今回は説明用に、B3セルの「私は人間だ、」という文字を「私は人間だ。」というように、句読点を置き換えします。
まず、「＝SUBSTITUTE(」と入力し、文字を変換したい対象のセルB3を選択します。選択したら、２つ目の引数を入力するために、カンマ(,)を続けてください。
=SUBSTITUTE(B3,
次に、SUBSTITUTE関数の第２引数「検索文字列」を指定します。検索文字列は、変換したい文字のことなので、「、」をダブルクォーテーションを囲んで入力します。
=SUBSTITUTE(B3,"、",
次に、SUBSTITUTE関数の第３引数「置換文字列」を指定します。置換文字列は、変換後の文字のことなので、「。」をダブルクォーテーションを囲んで入力します。
=SUBSTITUTE(B3,"、","。")
文字列の中に「、」は１つしかないので、SUBSTITUTE関数の第４引数「置換対象」は省略します。SUBSTITUTE関数が完成したら、「Enter」キーで数式を確定させます。
そうすると、B3セルの「私は人間だ、」という文字は「私は人間だ。」に変えることができました。
もし、B3セルに句読点「、」が２つある場合は、「私は。人間だ。」と変換されてしまいます。
このように、検索文字が複数存在している場合は、SUBSTITUTE関数の第４引数「置換対象」に「２」と入力することで、B2セルの２番目の「、」だけを「。」に置換することができます。
=SUBSTITUTE(B3,"、","。",2)
SUBSTITUTE関数の事例１(”A”を”B”に置換)
SUBSTITUTE関数の事例を５つ紹介していきます。
まずは、セルの中の１つ目の「A」を「B」に置き換える方法です。４つ目の引数「置換対象」に１つ目の「１」を入力するのがポイントです。
=SUBSTITUTE(B3,"A","B",1)
SUBSTITUTE関数の事例２(スペースやハイフンを削除)
SUBSTITUTE関数を使うことで、特定の文字を削除することができます。
例えば、電話番号の「090-9999-9999」や郵便番号の「312-0152」の「-」(ハイフン)を削除したり、「山田 太郎」の中の空白スペースを削除する、といった場合です。
方法は簡単で、下のSUBSTITUTE関数のように、２つ目の引数「検索文字列」に”-”や” ”(スペース)を入力し、３つ目の引数「置換文字列」を「””」と指定することで、特定の文字を削除することができます。
👇空白スペースを削除
=SUBSTITUTE(B3," ","")
👇ハイフン(-)を削除
=SUBSTITUTE(B4,"-","")
SUBSTITUTE関数の事例３(複数条件：２つの置換を実行)
「置換」機能とは異なり、SUBSTITUTE関数を使えば、２つの置換を同時に行うことができます。
SUBSTITUTE関数の第１引数の中に、もう１つのSUBSTITUTE関数を挿入することで、複数条件の置換を行うことができます。
数式の構文は以下のとおりです。このように関数の中に関数を挿入することを「入れ子(ネスト)」とよびます。
=SUBSTITUTE(SUBSTITUTE(文字列,検索文字列1,置換文字列1),検索文字列2,置換文字列2))
今回は説明用に、下の表のB列の文字を「A」を「C」に、「B」を「D」に置き換えします。
まず、SUBSTITUTE関数を挿入し、第１引数「文字列」の中にもう一つのSUBSTITUTE関数を挿入して、「A」を「C」に変換する条件を設定します。
=SUBSTITUTE(SUBSTITUTE(B3,"A","C"),
最後に、１つ目のSUBSTITUTE関数の第２引数と第３引数に「B」を「D」に置き換える条件を指定します。
=SUBSTITUTE(SUBSTITUTE(B3,"A","C"),"B","D")
そうすると、下の表のB列の文字を「A」を「C」に、「B」を「D」に置き換えすることができました。「検索と置換」機能ではこの用に複数条件での処理は行えませんが、SUBSTITUTE関数であれば可能です。
SUBSTITUTE関数の事例４(複数条件：３つの置換を実行)
上記で紹介し方法を応用すれば、SUBSTITUTE関数で３つ以上の置換も同時に行うことができます。
SUBSTITUTE関数の第１引数の中に、もう１つのSUBSTITUTE関数し、さらに２つ目のSUBSTITUTE関数の第１引数の中に３つ目のSUBSTITUTE関数を挿入することで、３つの複数条件の置換を行うことができます。(入れ子の利用)
数式の構文は以下のとおりです。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(文字列,検索文字列1,置換文字列1),検索文字列2,置換文字列2),検索文字列3,置換文字列3))
下の表のB列の文字を「A」を「D」に、「B」を「E」に、「C」を「F」に置き換えするには、下のSUBSTITUTE関数を挿入します。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,"A","D"),"B","E"),"C","F")
このように、SUBSTITUTE関数の中にSUBSTITUTE関数を挿入すれば、４つや５つ、さらにそれ以上の複数条件で文字の置換を同時に行うことができます。
SUBSTITUTE関数の事例５(半角スペースを全角スペースに統一)
SUBSTITUTE関数とTRIM関数を一緒に利用することで、文字の中に全角スペースと半角スペースが複数混ざっているセルを、全角スペースに統一することができます。
TRIM関数は、データの先頭や末尾、文字間のスペース/空白をまとめて調整するための関数です。
TRIM関数の中に、SUBSTITUTE関数を入れ子(ネスト）で利用して、以下のように関数を作成します。
=TRIM(SUBSTITUTE(B3," "," "))
SUBSTITUTE関数の第２引数「検索文字列」には、「” ”」( 半角スペース）を指定し、第３引数「置換文字列」には、「” ”」( 全角スペース）を指定します。
そうすると、SUBSTITUTE関数で、半角スペースが全角スペースに変換されてから、TRIM関数で余分なスペースを削除されるため、以下のように、文字間のスペースを統一することができます。
逆に、文字間を半角スペースに統一したい場合は、SUBSTITUTE関数の第２引数と第３引数を入れ替えるだけで、調整することができます。
この処理の詳しい方法は以下の記事で紹介しているので、興味のある方は参考にしてください。
文字を置き換えるSUBSTITUTE関数とREPLACE関数の違い
文字を置換するSUBSTITUTE関数とREPLACE関数の使い方を紹介しました。
この２つの関数の違いは、お分かりいただけたでしょうか。
SUBSTITUTE関数は、指定した文字を別の文字に変換するのに対し、REPLACE関数は指定した位置の文字を変換する関数というのが一番の違いです。
そのため、REPLACE関数は、文字数が変動する置換には不向きなため、SUBSTITUTE関数の方が使い勝手が良いかもしれません。
＝SUBSTITUTE(文字列,検索文字列,置換文字列,[置換対象])
＝REPLACE(文字列, 開始位置, 文字数, 置換文字列)
２つの関数の違い
・SUBSTITUTE関数は、指定した文字を別の文字に変換
・REPLACE関数は指定した位置の文字を変換
文字を置き換える【置換】機能
SUBSTITUTE関数やREPLACE関数の利用が苦手だと感じる方は、関数を使わず、「置換」機能を使う手もあります。
置換機能は、エクセルの基本コマンドの一つで、文字どおり、置換に特化した機能です。以下の記事で詳しく使い方と実例を紹介してますので、参考にしてください。
