文字列を置き換えるREPLACE関数とSUBSTITUTE関数

2017.10.27文字列操作関数

文字列の置き換えのためのExcelワークシート関数は、REPLACE関数とSUBSTITUTE関数の2種類があります。
この2つの関数の使い方と、使用例を紹介します。
実際に操作するファイルはこちらからダウンロードできます。

REPLACE関数

REPLACE関数は、文字の位置で文字列を置き換える関数です。
使い方は次の通りです。
=REPLACE(元の文字列,開始位置,文字数,置換文字)
例えば、元の文字が「あいう」で、「あ〇う」にしたいときは、2文字目から1文字を「〇」に置き換えるので、
=REPLACE("あいう",2,1,"〇")
になります。

では、実際にダウンロードしたファイルで操作してみましょう。
「REPLACE関数」シートをご覧ください。

元ファイル

 

セルE2に、セルA2の文字の、セルB2の開始位置から、セルC2の文字数分、セルD2の文字に置き換える数式を入力します。
=REPLACE(A2,B2,C2,D2)

関数入力

 

「あいうえおかきくけこ」の2文字目から1文字を「A」に置き換えたので「あAうえおかきくけこ」になりました。
さらに、セルE2に作成した関数を下にコピーしましょう。

コピー

 
  • セルE3は「あいうえおかきくけこ」の3文字目から2文字を「A」に置き換えたので「あいAおかきくけこ」になりました。
    2文字分を「A」の1文字に置き換えたので、全体の文字数が9文字と少なくなります。
  • セルE4は「あいうえおかきくけこ」の3文字目から3文字を「AB」に置き換えたので「あいABかきくけこ」になりました。
  • セルE5は「あいうえおかきくけこ」の2文字目から8文字を空白文字(何もないもの)に置き換えたので「あこ」になりました。
    文字列の間をなくすのに利用できます。

REPLACE関数は、実際には、次のような使用例が考えられます。
「あいうえおかきくけこ」から、指定した文字から何文字目までを「□」に置き換え、虫食い問題のようなものを作成する場合です。
すでに、セルA7に「あいうえおかきくけこ」、セルB7に開始位置、セルB8に文字数が入力されています。
「□」の文字数は最大10個の□の指定した文字からの文字数が必要なので、「□」が10個、セルA8に入力されています。
次のMID関数で、10個の「□」を開始位置から文字数分切り出して、置換文字にできます。
=MID(A8,B7,B8)
以上のことを踏まえてセルA9に虫食い問題を作成する計算式を作成します。
=REPLACE(A7,B7,B8,MID(A8,B7,B8))

虫食い問題

 

虫食いの文字列にすることができました。

SUBSTITUTE関数

SUBSTITUTE関数は、文字列を他の文字列に置き換える関数です。
使い方は次の通りです。
=SUBSTITUTE(元の文字列,検索する文字列,置き換える文字列[,位置])
位置は後で詳しく解説しますが、必要ない場合は省略しても大丈夫ということを覚えておいてください。
例えば、「何時」を「15時」に置き換えるには、「何時」の「何」を「15」に置き換えればいいので、
=SUBSTITUTE("何時","何","15")
になります。

「SUBSTITUTE関数」シートをご覧ください。

元ファイル

 

セルD2に、セルA2の文字列の中の、セルB2の文字列を、セルC2の文字列に置き換える数式を入力します。
=SUBSTITUTE(A2,B2,C2)

関数入力

 

セルD2は「あいうえおかきくけこ」の「い」を「B」に置き換えたので「あBうえおかきくけこ」になりました。
さらに、D3からD5までの範囲に作成した関数をコピーしましょう。

コピー

 
  • セルD3は「あいうえおかきくけこ」の「いうえ」を「しすせ」に置き換えたので「あしすせおかきくけこ」になりました。
  • セルD4は「あいうえおかきくけこ」の「いうえ」を空白文字(何もないもの)に置き換えたので「あおかきくけこ」になりました。
  • セルD5は「あいうえおあいうえお」の「いうえ」を「BCD」に置き換えたので「あBCDおあBCDお」になりました。「いうえ」は2か所ありましたので、2か所とも「BCD」に置き換えました。

どちらかの「いうえ」だけを置き換えしたいときは、関数に[位置]を設定します。
2番目の「いうえ」だけ置き換えしたいので、2を指定します。
セルD6に次の計算式を入力しましょう。
=SUBSTITUTE(A6,B6,C6,2)

関数入力

 

「あいうえおあいうえお」の2番目の「いうえ」だけ「BCD」に置き換えたので「あいうえおあBCDお」になりました。

SUBSTITUTE関数を使った単位の削除

「100円」などの単位がある数値は文字データなので、計算ができません。
そこで、SUBSTITUTE関数を使って、単位を空白文字に置き換えることで、数字部分だけ抜き出すことができます。
ただし、SUBSTITUTE関数の答えも文字データになるので、VALUE関数を使って数値データに変換します。
実際に動作を見てみましょう。
セルB10に次の計算式でセルB8とセルB9の合計を求めます。
=SUM(B8:B9)

文字列の合計

 

文字データの計算になるので、答えは0という求めたい答えと違う値になってしまいました。
そこで、セルC8に次の計算式で、セルB8から「円」の文字を無くし数値データに変換します。
=VALUE(SUBSTITUTE(B8,"円",""))

関数入力

 

「1250円」という文字データが、「1250」という数値データに変換されました。
では、この計算式をセルC9にコピーしましょう。

コピー

 

同様に「800円」という文字データが、「800」という数値データに変換されました。
セルC10に次の計算式でセルC8とセルC9の合計を求めます。
=SUM(C8:C9)

合計

 

合計を求めることができました。

ひな型文書へキーワードを差し込む

SUBSTITUTE関数を使うと、ひな型文書にキーワードを差し込むことができます。
セルA12のひな型文書の「<name>」欄に、セルC12に記載されている人名を差し込む場合、次の計算式になります。
=SUBSTITUTE(A12,"<name>",C12)
セルD12に入力してみましょう。

ひな形への差し込み

 

ひな型文書の「<name>」欄にセルC12の人名が差し込まれました。
この結果にさらに追加し、「<item>」欄にセルC13の商品名を差し込みたい場合、SUBSTITUTE関数の中にSUBSTITUTE関数を使う、次のような計算式とします。
=SUBSTITUTE(SUBSTITUTE(A12,"<name>",C12),"<item>",C13)
セルD12の計算式をこの計算式に変更してみましょう。

ひな形への差し込み2

 

ひな型文書の「<item>」欄にセルC13の人名が差し込まれました。
「<br>」を改行に置き換えすることもできます。
上記の計算式に「<br>」を改行に変更するSUBSTITUTE関数を追加して、次の計算式になります。
改行はCHAR関数で文字番号10番を指定します。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A12,"<name>",C12),"<item>",C13),"<br>",CHAR(10))
セルD12の計算式をこの計算式に変更してみましょう。

ひな形への差し込み3

 

ひな型文書の「<br>」欄が改行されました。
Excelでセル内改行を表示する場合は、書式設定でセルの配置を[折り返して全体を表示する]の設定を行う必要があります。今回はあらかじめ設定してあります。
[折り返して全体を表示する]の詳細はこちらの記事で詳しく説明しています。

まとめ

今回は文字を置き換える関数、REPLACE関数とSUBSTITUTE関数について解説しました。
REPLACE関数は文字の位置と文字数で置き換え、SUBSTITUTE関数は文字列自体を指定して置き換えるという違いで使い分けていきます。
今回紹介した方法の中で、実際に活用するケースでは、SUBSTITUTE関数で空白文字に変換し、指定した文字を削除するという使い方が最も多いと思いますので、使い方と併せて、使用法を暗記しておくとよいでしょう。
また、REPLACE関数は10個並んだ「〇」のうち、指定した位置の「〇」を「●」に置き換えることができるので、簡易的なグラフ表示にも使えます。

Excel業務をもっと「ラク」にするなら

Excel業務を自動化したい!
Excelをデータベース化したい!

 

Back to top