Excelで部署名、記号、表記ゆれなどをまとめて直したいとき、元データを直接置換する前にSUBSTITUTE関数で変換結果を別セルに作ると、内容を確認しながら安全に整えられます。検索と置換は一気にセルの値を書き換えますが、SUBSTITUTE関数は数式として「この文字をこの文字に置き換えた結果」を返すため、元の文字列を残したまま試せるのが利点です。
SUBSTITUTE関数でできること
SUBSTITUTE関数は、文字列の中にある指定した文字や語句を、別の文字列に置き換えて返すExcelの文字列関数です。対象はセル参照でも、数式内に直接入力した文字列でも指定できます。
たとえば、A2に「営業部-東日本」と入っている場合、ハイフンを全角の区切りに変えたり、「営業部」を「営業本部」に変えたりできます。元のセルを編集せず、別セルに結果を出せるため、名簿、商品名、コード、住所の一部などを確認しながら整える作業に向いています。
基本の書き方は次の形です。
- 文字列：置き換えたい文字を含むセル、または文字列を指定します。
- 検索文字列：探して置き換える元の文字列を指定します。
- 置換文字列：検索文字列の代わりに表示したい文字列を指定します。
- 置換対象：同じ文字列が複数あるとき、何番目だけを置き換えるかを必要に応じて指定します。
4つ目の置換対象を省略すると、該当する文字列はすべて置き換わります。特定の1回分だけ置き換えたい場合は、1、2、3のように番号を入れます。
すべての一致を置き換える基本手順
作業前に、元データの列は残しておくと安心です。重要な一覧であれば、ブックを保存するか、シートをコピーしてから操作してください。SUBSTITUTE関数自体は元のセルを書き換えませんが、あとで値として貼り付ける場合に元データへ上書きする可能性があります。
- 置き換え結果を表示する空白列を用意します。
- 結果を出したいセルを選択します。
- 半角の等号に続けてSUBSTITUTE関数を入力します。
- 文字列には元データのセルを指定します。
- 検索文字列と置換文字列は、二重引用符で囲んで入力します。
- Enterキーで確定し、結果を確認します。
- 必要に応じてフィルハンドルで下の行へコピーします。
例として、A2の「営業部-東日本」に含まれる「-」を「／」に置き換えるなら、結果用のセルに次のように入力します。
=SUBSTITUTE(A2,”-“,”／”)
この数式では、A2の中にあるハイフンがすべて「／」に置き換わります。A列の元データは残るので、B列などに変換結果を作り、想定どおりかを見比べられます。
何番目だけを置き換えるか指定する
同じ文字が複数回出てくる文字列では、4つ目の引数で置換対象を指定できます。たとえば、A2に「2026-09-18」と入っていて、最初のハイフンだけをスラッシュに変える場合は次のようにします。
=SUBSTITUTE(A2,”-“,”/”,1)
置換対象に1を指定しているため、最初に見つかったハイフンだけが置き換わります。2を指定すれば2番目だけ、3を指定すれば3番目だけが対象です。省略した場合はすべて置き換わるため、日付、コード、住所のように同じ記号が複数ある文字列では、置換対象を指定するかどうかを先に決めておくと失敗を減らせます。
置換対象は「文字列内で何回目に見つかったか」を数えます。セルの列番号や文字数ではありません。結果が思った場所と違う場合は、検索文字列が余分な空白や全角半角の違いを含んでいないかも確認してください。
置換後に元データへ戻すときの注意
SUBSTITUTE関数で作った結果を元の列に戻したい場合、数式のまま貼り付けると参照先が変わったり、元データを消したあとに結果が崩れたりすることがあります。確定した結果だけを残したいときは、結果範囲をコピーし、貼り付け先で値として貼り付けます。
値として貼り付ける前には、次の点を確認してください。
- 変換前の列を残す必要があるか。
- 先頭の0、日付、コードなどが意図しない表示形式に変わっていないか。
- 空白セルや例外的な表記にも同じ数式をコピーしてよいか。
- 置換対象を省略したことで、置き換えたくない箇所まで変わっていないか。
確認用にフィルターを使って空白や特定の語句を絞り込むと、変換漏れを見つけやすくなります。大量のデータで不安がある場合は、先に数行だけで試し、結果の列を見てから全体へコピーすると安全です。
SUBSTITUTE関数とREPLACE関数の使い分け
文字列の中の「特定の文字や語句」を探して置き換えたいときは、SUBSTITUTE関数が向いています。部署名の変更、記号の統一、不要な語句の削除など、探す文字列が決まっている場面です。置換文字列に空の文字列を指定すれば、指定した文字を取り除く用途にも使えます。
一方、文字の位置と文字数を指定して置き換えたいときはREPLACE関数を使います。たとえば、コードの先頭3文字を別の文字に変える、固定長の番号の一部だけを差し替える、といった作業です。REPLACE関数は何文字目から何文字分を置き換えるかを指定するため、探す文字列そのものではなく位置が基準になります。
「何を探すか」で考えるならSUBSTITUTE関数、「どの位置を変えるか」で考えるならREPLACE関数、と分けて考えると選びやすくなります。
うまく置き換わらないときの確認点
結果が変わらない場合は、検索文字列が元データと完全に一致していない可能性があります。Excelでは、全角と半角、余分な空白、似た記号の違いがあると別の文字列として扱われます。ハイフンに見えても、長音、マイナス、別の横線が混ざっていることがあります。
また、検索文字列や置換文字列を直接数式に書く場合は、文字列を二重引用符で囲みます。セルに入っている語句を検索文字列として使う場合は、二重引用符ではなくセル参照を指定できます。複数条件を順番に置き換えたいときはSUBSTITUTE関数を入れ子にできますが、数式が長くなるほど確認しにくくなります。まずは1つの置換だけで結果を確認し、必要に応じて段階的に増やすと扱いやすくなります。
SUBSTITUTE関数は、元データを残したまま表記を整えたいときに便利です。置換対象を省略するか指定するか、結果を値として残すかを確認しながら進めれば、検索と置換で直接書き換えるよりも落ち着いて文字列を整理できます。