Excelで文字を置換する方法！SUBSTITUTEとREPLACEの違い

けど、そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する

についてまとめます！

完成イメージ

型番や住所の文字を置換したいとき、1文字ずつ手で直すのは手間がかかり、修正ミスも起きやすくなります。
今回は
・SUBSTITUTE関数（文字を指定して置換・全部／回数指定もできる）と、REPLACE関数（位置と文字数で置換）の違いと使い方
をゴールにします。
目的に合わせて2つの関数を使い分けられるようになります。

上の画像が完成形です。元の型番（A列）に対して、SUBSTITUTEとREPLACEで置換した結果（B〜E列）が並んで表示されています。

なお、置換を入れ子にして複数の文字をまとめて変える方法と、VBAのReplaceで一括置換する方法、うまくいかないときの対処法（トラブルシュート）もあわせてご紹介します。
早速実装して試してみましょう！

STEP1. 元データを用意する

まずは置換したい型番の一覧を用意します。
A列が元の型番で、10件のデータです。
B列から右は、これから置換した結果を表示するエリアとして空けておきます。
型番は「AB-1001-JP」のように、英字・数字・ハイフンが組み合わさった形式です。
この形だと、ハイフンを消したり一部を置き換えたりと、置換の練習にぴったりですね。

下の表をコピーして、Excelのシートに貼り付けるとそのまま試せます。

※ 数式で求める列は空にしています。すでに値が入っている列はそのまま残して試せます。

STEP2. SUBSTITUTE関数で「文字を指定して」置換する

まずはSUBSTITUTE関数です。
これは「この文字を、この文字に置き換える」という、文字そのものを指定する置換です。
ハイフン「-」を消したいので、B2セルに次のように入力します。

=SUBSTITUTE(A2, "-", "")

第1引数が元の文字列、第2引数が探す文字、第3引数が置き換える文字です。
置き換える文字を「””（何もない）」にしているので、ハイフンが消えて「AB1001JP」になります。
B11までコピーすれば、10件すべてのハイフンが取れました。
「特定の文字をまとめて消す」なら、このSUBSTITUTEが第一候補です。
探している文字が見つからなくてもエラーにはならず、元の文字列がそのまま返ります。
実際の入力画面がこちらです。

数式はどのセルに入れる？

セルを選んで、数式バーにそのまま入力するだけです。
入力中は引数のヒントが表示されるので、順番を確認しながら進められます。

STEP3. SUBSTITUTEは「回数」も指定できる

SUBSTITUTEは、4つ目の引数で「何個目の文字を置き換えるか」を指定できます。
ハイフンが2つある型番で、最初の1つだけを消したい場合はこうです。
C2セルに入力して、C11までコピーします。

=SUBSTITUTE(A2, "-", "", 1)

最後の「1」が「1つ目だけ置き換える」という意味です。
すると「AB-1001-JP」のうち、最初のハイフンだけが消えて「AB1001-JP」になりました。
4つ目を省略すれば全部置換、数字を入れればその個所だけ、と使い分けられます。
「2個目だけ消したい」ときは「2」と入れます。
同じ関数でも、置換する範囲を細かくコントロールできるのがSUBSTITUTEの強みですね。

STEP4. REPLACE関数で「位置と文字数」を指定して置換する

次はREPLACE関数です。
こちらは「何文字目から、何文字分を、この文字に置き換える」という、位置で指定する置換です。
型番の先頭2文字「AB」を「XX」に変えてみましょう。
D2セルに次のように入力し、D11までコピーします。

=REPLACE(A2, 1, 2, "XX")

「1文字目から2文字分」を「XX」に置き換える、という意味です。
「AB-1001-JP」が「XX-1001-JP」になりました。
SUBSTITUTEが「文字そのもの」を探すのに対し、REPLACEは「場所」を指定するのが大きな違いです。
先頭に決まった文字を差し込みたいときなど、位置で決まる置換はREPLACEが向いています。

REPLACEの入力画面

実際の入力画面です。
第2引数が「開始位置」、第3引数が「文字数」になっています。
「どこから何文字」を数えて指定するので、位置が決まっている置換に向いています。

STEP5. 入れ子にして複数の文字をまとめて置換する

「ハイフンを消して、さらにABもXYに変えたい」など、置換を2つ重ねたいときは入れ子にします。
入れ子とは、関数の中に別の関数を入れる書き方です。
E2セルに次のように入力し、E11までコピーします。

=SUBSTITUTE(SUBSTITUTE(A2, "-", ""), "AB", "XY")

内側のSUBSTITUTEでハイフンを消し、その結果に対して外側のSUBSTITUTEで「AB」を「XY」に置換しています。
「AB-1001-JP」が「XY1001JP」になりました。
置換は内側から外側の順に進むので、順番を入れ替えると結果が変わります。
2つくらいなら入れ子で十分ですが、3つ以上になると式が見づらくなるのでVBAがラクです。

SUBSTITUTEとREPLACEの違い

2つの関数の違いを表にまとめました。
どちらも「置換」ですが、指定のしかたが根本的に違います。

VBAのReplaceで一括置換する

件数が多く、同じ置換を繰り返すなら、VBAのReplaceが手軽です。
Alt+F11でVBAエディターを開き、標準モジュールに次のコードを貼り付けます。

Sub SubHyphen()
    Dim r As Long
    For r = 2 To 11
        Cells(r, 2).Value = Replace(Cells(r, 1).Value, "-", "")
    Next r
End Sub

VBAのReplaceは、ExcelのSUBSTITUTEと同じ「文字を指定する」置換です。
実行すると、B列にハイフンを消した型番を一気に入れられました。
ボタンに登録しておけば、クリック1つで同じ整形ができます。

タカヒロ

VBAのReplaceは「文字を指定する」タイプなので、SUBSTITUTEと同じ感覚で使えます。
「場所で指定したい」ときだけREPLACEの出番です。
どんな置換をしたいかで選べば、もう迷いませんよ。

置換で「ワイルドカード」は使える？

COUNTIFやSUMIFでは「*（任意の文字）」などのワイルドカードが使えます。
しかしSUBSTITUTEやREPLACEはワイルドカードに対応していません。
たとえば =SUBSTITUTE(A2, “*”, “”) と書いても、記号としての「*」を探すだけで、「任意の文字」としては扱われません。
「〜で始まる文字をまとめて置換」のようなことは、これらの関数ではできないと覚えておきましょう。
画面上でなら、Ctrl+H（検索と置換）のダイアログに「ワイルドカードを使用する」のチェックがあるので、そちらが便利です。

うまくいかないときの対処法（トラブルシュート）

実際に試すと、思ったとおりに動かないこともあるかもしれません。
よくあるつまずきと対処法をまとめました。

関数の説明

今回使う2つの関数をまとめます。
どちらも置換の基本になる関数なので、違いを押さえておきましょう。

SUBSTITUTE関数（サブスティチュート）

=SUBSTITUTE(A2, "-", "")

REPLACE関数（リプレイス）

=REPLACE(A2, 1, 2, "XX")

完成

A列の型番に対して、4種類の置換結果が正しく並びました。
「-を全部削除」「最初の-だけ削除」「先頭2文字をXXに」「入れ子で複数置換」と、結果を比べて確認できます。
思ったとおりに動きましたか？
SUBSTITUTEとREPLACEの役割の違いがつかめれば、型番や住所の整形がぐっとラクになります。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

「この文字を変えたい」ならSUBSTITUTE、「何文字目を変えたい」ならREPLACEと覚えておけば十分です。
回数指定や入れ子、VBAまで使えるようになると、手作業の修正が減ってミスも防げます。
まずは今回のサンプル表で、ぜひ一度試してみてください。
置換を使いこなすと、日々のデータ整形がぐっとラクになりますよ。
また、他にも便利なExcelの時短テクニックがありますので、よろしければご参照頂ければと思います。