Excel関数で文字列を置換する方法をお探しですね。

広告

Excelで文字を置き換えたり削除したりする方法【SUBSTITUTE関数とREPLACE関数】

Excelで住所や電話番号、商品コードなんかを扱っていると、「この文字、別の文字に変えたいな」とか「この記号、邪魔だから消したいな」って思うこと、ありますよね。

そんなときに使えるのが、**SUBSTITUTE関数**と**REPLACE関数**です。

どっちも文字を置き換える関数なんですが、使い方がちょっと違います。

– **SUBSTITUTE関数**は「この文字を探して置き換える」
– **REPLACE関数**は「左から○文字目を置き換える」

この記事では、2つの関数の違いと使い方を、実際の例を見ながらわかりやすく説明していきます。

SUBSTITUTE関数とREPLACE関数、何が違うの?

この2つ、どちらも文字を置き換える関数なんですが、**指定の仕方**が全然違います。

**SUBSTITUTE関数**は、セルの中から「特定の文字列」を探して、それを別の文字に置き換えます。

たとえば、「100円」から「円」を消したいとか、「(株)」を「株式会社」に統一したいとか、**置き換えたい文字がはっきりしているとき**に便利です。

**REPLACE関数**は、「左から○文字目から、△文字分を置き換える」という指定をします。

電話番号の真ん中を「****」で隠したいとか、商品コードの最初の3文字だけ変えたいとか、**文字の位置が決まっているデータ**に向いています。

書き方を見比べてみよう

**SUBSTITUTE関数**
“`
=SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])
“`
– 「検索文字列」に置き換えたい元の文字を書きます
– 「置換文字列」に新しく表示したい文字を書きます
– 最後の「置換対象」は省略できて、同じ文字が何回も出てくるときに「何番目だけ置き換えるか」を指定できます

**REPLACE関数**
“`
=REPLACE(文字列, 開始位置, 文字数, 置換文字列)
“`
– 「開始位置」と「文字数」を数字で指定します
– 文字の内容じゃなくて、位置で指定するのがポイントです

**簡単に言うと**
– 「この文字を探して変えたい」→ **SUBSTITUTE関数**
– 「○文字目から変えたい」→ **REPLACE関数**

って覚えておけばOKです。

SUBSTITUTE関数の使い方：特定の文字を探して置き換える・消す

SUBSTITUTE関数は、表記のゆれを直したり、いらない文字を消したりするときにすごく便利です。

基本的な使い方

たとえば、A1セルに「東京都新宿区西新宿」って入っていて、「新宿」を「渋谷」に変えたいとき。

“`
=SUBSTITUTE(A1,”新宿”,”渋谷”)
“`

これで、A1セルの中の「新宿」を探して、ぜんぶ「渋谷」に置き換えてくれます。

文字を削除する方法

「A-B-C」のハイフンを消したいときは、こうします。

“`
=SUBSTITUTE(A1,”-“,””)
“`

置き換え後の文字を「””」(空っぽ)にすると、削除できるんです。

この「””」は「何もない文字列」って意味で、ダブルクォーテーションを2つ並べて書きます。

これを使えば、いらない記号やスペース、単位なんかをまとめて消せます。

実務でよくある使い方：単位を削除して計算できるようにする

A1セルに「1250円」って入っているとき、計算で使うために「円」を消したい場合。

“`
=SUBSTITUTE(A1,”円”,””)
“`

これで「1250」って文字列が取り出せます。

ただ、このままだと文字列として扱われることがあるので、計算に使うなら**VALUE関数**で数値に変換しておくと安心です。

“`
=VALUE(SUBSTITUTE(A1,”円”,””))
“`

改行を削除する

セルの中の改行を消したいときは、改行を表す「CHAR(10)」を使います。

“`
=SUBSTITUTE(A1,CHAR(10),””)
“`

住所データとかCSVから取り込んだデータには、見えない改行や余分な空白が混ざってることがあるので、この方法でデータを整えられます。

同じ文字が何回も出てくる場合

A1セルに「Excelは便利なExcelです」って入っていて、すべての「Excel」を「エクセル」に変えるなら、

“`
=SUBSTITUTE(A1,”Excel”,”エクセル”)
“`

でOKです。

でも、**2番目の「Excel」だけ**変えたいときは、

“`
=SUBSTITUTE(A1,”Excel”,”エクセル”,2)
“`

って書きます。

最後に「2」を付けると、2回目に出てきた文字だけが置き換わります。

複数の文字をまとめて置き換える

「ノートパソコン」と「デスクトップパソコン」をどっちも「パソコン」にしたいとき。

SUBSTITUTE関数を入れ子にすれば、いらない部分を順番に消せます。

“`
=SUBSTITUTE(SUBSTITUTE(A1,”ノート”,””),”デスクトップ”,””)
“`

REPLACE関数の使い方：位置を指定して置き換える・消す

REPLACE関数は、文字の内容じゃなくて、**位置と文字数**を指定して置き換える関数です。

基本的な使い方

A1セルに「あいうえお」って入っていて、2文字目から1文字分を「〇」に変えたいとき。

“`
=REPLACE(A1,2,1,”〇”)
“`

結果は「あ〇うえお」になります。

– 「2」は左から2文字目
– 「1」はそこから1文字分を置き換える
– 「〇」は置き換え後の文字

置き換える文字は1文字じゃなくてもOKです。

“`
=REPLACE(A1,2,2,”ABC”)
“`

これなら、2文字目から2文字分を「ABC」に置き換えるので、元の文字数と結果の文字数が変わることもあります。

よくある使い方：電話番号の一部を伏せ字にする

A1セルに「09012345678」って電話番号があって、4文字目から4文字分を「****」で隠したいとき。

“`
=REPLACE(A1,4,4,”****”)
“`

結果は「090****5678」になります。

個人情報を隠して管理するのに便利ですね。

ハイフン付きの「090-1234-5678」の場合は、5文字目から4文字分が真ん中の番号なので、

“`
=REPLACE(A1,5,4,”****”)
“`

って書きます。

**ポイント**
REPLACE関数は、データの形式がそろってるほど使いやすいです。

電話番号の桁数やハイフンの有無がバラバラだと、先にSUBSTITUTE関数でハイフンを消してからREPLACE関数を使う、みたいな工夫が必要になります。

文字を削除する

REPLACE関数でも、置き換え後の文字を空っぽ「””」にすれば削除できます。

A1セルに「商品A-001」って入っていて、6文字目のハイフンを消したいとき。

“`
=REPLACE(A1,6,1,””)
“`

ただし、REPLACE関数で削除する場合は、**消したい文字がいつも同じ位置にある**ことが前提です。

商品コードの形式が「ABC-001」「A-0001」「商品-001」みたいにバラバラだと、同じ式では意図しない文字を消しちゃう可能性があります。

だから、
– **位置が固定されているデータ** → REPLACE関数
– **位置が変わっても同じ文字を探して削除したい** → SUBSTITUTE関数

って使い分けるのが基本です。

実務で迷わない使い分けのコツと注意点

どっちを使えばいいか迷ったら

「置き換えたい対象が**文字で判断できるか**、**位置で判断できるか**」を考えてみてください。

**SUBSTITUTE関数が向いてる場合**
– 「株式会社」「円」「-」「スペース」「改行」など、消したい・置き換えたい文字がはっきりしてる

**REPLACE関数が向いてる場合**
– 「先頭3文字を変更する」「5文字目から4文字を伏せる」「末尾2文字を削除する」など、位置と文字数で処理したい

実務では、2つを組み合わせることもあります。

たとえば、SUBSTITUTE関数でいらない記号を消してデータを整えて、そのあとREPLACE関数で一部を伏せ字にする、みたいな感じです。

どっちか一方だけ覚えるより、違いを理解しておく方が、いろんなデータ処理に対応できますよ。

「検索と置換」機能との違い

Excelには、Ctrl＋Hで開ける「検索と置換」機能もあります。

これは、選択範囲やシート全体の文字列を一気に書き換えたいときに便利です。

**ただし注意点があります**
– 置換機能は**元データそのものを変更する**ので、あとから元に戻しにくい
– SUBSTITUTE関数やREPLACE関数は、元のセルを参照して「置換後の結果」を別セルに表示する仕組み

だから、
– 元データを残したまま変換結果を確認できる
– 参照元の値が変わると、結果も自動で更新される

毎月取り込む売上データや顧客データの整形では、置換機能で毎回手作業するより、関数を用意しておく方がミスを減らせます。

よくある注意点

**SUBSTITUTE関数の注意点**
– 大文字と小文字を区別します
– 「Excel」を探すとき、「EXCEL」や「excel」は別の文字として扱われます
– 全角と半角も別の文字です
– 「Ａ」と「A」、「－」と「-」は違う文字
– 意図した置換ができないときは、見た目が似てる別文字や、セル内に余分なスペースが入ってないか確認してみてください

**REPLACE関数の注意点**
– 開始位置や文字数の指定ミスがよくあります
– データの形式がそろってない場合に同じ式をコピーすると、必要な文字まで消しちゃうことがあります
– 大量のデータに適用する前に、数件のサンプルで結果を確認しましょう

必要に応じて、LEFT関数、RIGHT関数、MID関数、VALUE関数なんかと組み合わせると、もっと柔軟に対応できます。

まとめ：削除も置換も考え方は同じ

削除と置換は、実は同じことです。

**「置換後の文字列を何にするか」で決まる**だけなんです。

– 別の文字に変えれば → **置換**
– 空文字「””」に変えれば → **削除**

この考え方を押さえておくと、SUBSTITUTE関数もREPLACE関数も一気に理解しやすくなります。

**簡単に言うと**
– 特定の文字を探して消したい → `=SUBSTITUTE(A1,”削除したい文字”,””)`
– 決まった位置の文字を消したい → `=REPLACE(A1,開始位置,文字数,””)`

Excelの文字列処理は、最初は関数名が似てて難しく感じるかもしれませんが、判断基準はすごくシンプルです。

**「文字を指定するのがSUBSTITUTE、位置を指定するのがREPLACE」**

これだけ覚えておけば、日々のデータ修正や表記統一を効率よく進められますよ。

慣れてくると、「あ、これはSUBSTITUTEだな」「これはREPLACEの方がいいな」って自然に判断できるようになります。

ぜひ実際に使ってみて、自分のものにしてくださいね!

広告