「顧客データのフリガナ欄に全角カナと半角カナが混在している」「システムから取り込んだCSVの電話番号に不要なハイフンが含まれている」「製品コードの旧プレフィックスを新プレフィックスに一括で書き換えたい」「入力ミスで混入したスペースや記号をまとめて除去したい」——こうした文字列の置換・修正作業は、ExcelのSUBSTITUTE関数・REPLACE関数を使えば、元データを変更することなく数式一本で解決できます。
Excelで文字列を一括置換する方法として「Ctrl+H」の検索・置換機能がよく使われますが、この操作はセルの値そのものを書き換えるため、元データが失われてしまいます。SUBSTITUTE関数とREPLACE関数は、セルの値を保持したまま「置換後の文字列」を数式として別の列に生成できることが最大の利点です。加工前と加工後を並べて比較できる、条件によって置換内容を動的に変えられる、といった柔軟な運用が可能になります。MOS Excel試験でも文字列操作関数は毎回出題される定番分野であり、SUBSTITUTE・REPLACEの基本操作は確実に押さえておく必要があります。本記事では、SUBSTITUTE関数とREPLACE関数の構文と引数の意味・基本的な使い方・業務シナリオ別の実務パターン・TRIM・CLEAN関数との使い分け・MOS Excel試験の出題傾向と対策まで、2026年最新版で徹底解説します。
「データクリーニングの手作業をなくしたい」「MOS試験の文字列操作問題を確実に得点したい」という方は、ぜひ最後までお読みください。
SUBSTITUTE関数とREPLACE関数：2つの役割を整理する
まず、SUBSTITUTE関数とREPLACE関数の違いを整理しましょう。名前が似ていますが、「何を基準に置換するか」がまったく異なります。
| 関数 | 置換の基準 | 主な用途 | 注意点 |
|---|---|---|---|
| SUBSTITUTE | 文字列の「内容」で置換 | 特定の文字・単語を別の文字に変える | 同じ文字が複数ある場合の制御に第4引数が必要 |
| REPLACE | 文字列の「位置と文字数」で置換 | 固定位置から指定文字数を別の文字に変える | 文字の内容ではなく場所で指定するため形式が固定されているデータに向く |
SUBSTITUTE関数は「この文字を、あの文字に変える」という内容ベースの置換です。一方REPLACE関数は「この位置から何文字分を、あの文字に変える」という位置ベースの置換です。どちらを使うかは「置換したい箇所が固定の内容か、固定の位置か」で判断します。たとえば「電話番号のハイフンを除去する」はSUBSTITUTEが適切で、「品番の先頭3文字を別の文字列に置き換える」はREPLACEが適切な場面です。
SUBSTITUTE関数の基本構文と使い方
構文と引数の意味
SUBSTITUTE関数の構文は次のとおりです。
=SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])| 引数 | 必須 | 内容 |
|---|---|---|
| 文字列 | 必須 | 置換対象となる文字列またはセル参照 |
| 検索文字列 | 必須 | 検索する文字列（大文字・小文字を区別する） |
| 置換文字列 | 必須 | 置換後の文字列。文字を削除したい場合は””（空文字）を指定 |
| 置換対象 | 任意 | 同じ検索文字列が複数ある場合に何番目を置換するか（省略するとすべて置換） |
第4引数「置換対象」は省略可能で、省略した場合は検索文字列に一致するすべての箇所が置換されます。特定の出現箇所だけを置換したい場合は、対象とする番号を整数で指定します。また、SUBSTITUTE関数は大文字・小文字を区別するため、”Excel”と”excel”は別の文字列として扱われる点に注意が必要です。
基本的な使用例
次の表はSUBSTITUTE関数の代表的な使い方を示しています。
| 目的 | 数式の例 | 結果の例 |
|---|---|---|
| “株式会社”を”(株)”に置換 | =SUBSTITUTE(A1,”株式会社”,”(株)”) | 田中(株)（A1=”田中株式会社”の場合） |
| 電話番号のハイフンをすべて除去 | =SUBSTITUTE(A1,”-“,””) | 0312345678（A1=”03-1234-5678″の場合） |
| スペースをすべて除去 | =SUBSTITUTE(A1,” “,””) | 山田太郎（A1=”山田 太郎”の場合） |
| 2番目の”/”だけを”-“に置換 | =SUBSTITUTE(A1,”/”,”-“,2) | 2026/01-05（A1=”2026/01/05″の場合） |
「置換文字列」に空文字(“”)を指定することで、文字列の「削除」として機能させられます。不要な記号・スペース・特定のコードをデータから取り除く操作に頻繁に使われる重要なテクニックです。
REPLACE関数の基本構文と使い方
構文と引数の意味
REPLACE関数の構文は次のとおりです。
=REPLACE(文字列, 開始位置, 文字数, 置換文字列)| 引数 | 必須 | 内容 |
|---|---|---|
| 文字列 | 必須 | 置換対象となる文字列またはセル参照 |
| 開始位置 | 必須 | 置換を開始する文字位置（左端が1） |
| 文字数 | 必須 | 置換する文字数。0を指定すると文字の「挿入」になる |
| 置換文字列 | 必須 | 置換後の文字列。削除したい場合は””を指定 |
REPLACE関数の「文字数」に0を指定すると、指定した位置に文字を「挿入」する操作になります。これはExcel関数の中でも特殊な動作で、桁区切りの追加や日付・コードへのセパレータ挿入に応用できます。4つの引数はすべて必須であり、省略できるものがない点もSUBSTITUTE関数との違いです。
基本的な使用例
次の表はREPLACE関数の代表的な使い方を示しています。
| 目的 | 数式の例 | 結果の例 |
|---|---|---|
| 品番の先頭3文字を”NEW”に変更 | =REPLACE(A1,1,3,”NEW”) | NEW-456（A1=”OLD-456″の場合） |
| 電話番号の市外局番を伏せ字に | =REPLACE(A1,1,2,”**”) | **-1234-5678（A1=”03-1234-5678″の場合） |
| 指定位置から3文字を削除 | =REPLACE(A1,5,3,””) | 山田太郎（A1=”山田 太郎”3文字分の場合） |
| 4文字目に”-“を挿入 | =REPLACE(A1,4,0,”-“) | 123-4567（A1=”1234567″の場合） |
REPLACE関数は品番・コード・電話番号など「形式が統一されているデータ」の編集に向いています。文字列の内容ではなく場所で操作するため、セルによって文字数が異なるデータにはSUBSTITUTE関数のほうが安全です。REPLACEを使う前に、対象データの文字数と位置が一定かどうかを確認する習慣をつけましょう。
実務シナリオ別の活用パターン
不要スペース・記号の一括除去
顧客データや社員マスタの氏名フィールドには、入力者によって半角スペースと全角スペースが混在していることがあります。SUBSTITUTE関数をネストして両方を一度の数式で除去できます。
=SUBSTITUTE(SUBSTITUTE(A1," ","")," ","")外側のSUBSTITUTEが全角スペースを除去し、内側が半角スペースを除去します。このようにSUBSTITUTEを入れ子にすることで、複数の文字を1つの数式で処理できます。電話番号・郵便番号のハイフンや括弧を除去して数字のみにする場合も同様のパターンが使えます。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"(",""),")","")
三重ネストにすることで「-」「(」「)」の3種類を一度に除去できます。「03-1234-5678」も「(03)1234-5678」も同じ数式で「0312345678」に変換されます。
電話番号・コードへのセパレータ挿入
システムから取り込んだ電話番号が「0312345678」のようにハイフンなしで格納されている場合、REPLACE関数を使ってハイフン付きの表示形式に変換できます。先頭が「03」「06」などの2桁市外局番の場合、「03-1234-5678」形式にするには次のように記述します。
=REPLACE(REPLACE(A1,3,0,"-"),8,0,"-")まず内側のREPLACEが3文字目の手前（文字数0）に”-“を挿入し、「03-12345678」にします。その結果文字列の8文字目の手前にもう1つ”-“を挿入することで「03-1234-5678」が完成します。桁数が固定されているコードや番号の整形に有効なパターンです。郵便番号「1234567」を「123-4567」形式にする場合も同様に=REPLACE(A1,4,0,"-")で対応できます。
品番コード・分類コードの旧形式を新形式に一括変換
製品コードのプレフィックスが「P-」から「PRD-」に変更になった場合など、大量データの一括更新にSUBSTITUTE関数が威力を発揮します。
=SUBSTITUTE(A1,"P-","PRD-",1)第4引数に「1」を指定することで、コード中に偶然”P-“という文字列が複数出現する場合でも、先頭の1箇所だけを置換して誤置換を防げます。データの性質上、コードの先頭だけを置換したい場合はこのように第4引数を明示的に指定することが推奨されます。
SUBSTITUTE多段ネストで複数パターンを一括置換
取り込んだデータに複数種類の不要文字が混在している場合、SUBSTITUTEを多段ネストして1つの数式で処理します。次の例では全角括弧を半角に統一するパターンです。
=SUBSTITUTE(SUBSTITUTE(A1,"（","("),"）",")")ネストの深さに制限はありませんが、保守性を考えると3～5段程度が実用的な限界です。それ以上の置換が必要な場合は、ヘルパー列を複数設けてステップごとに処理するほうが、数式の可読性と後からの修正容易性が高まります。
日付・年月日文字列のフォーマット変換
「2026年06月08日」のような日本語日付文字列を「2026-06-08」形式に変換する場合、SUBSTITUTE関数を使って「年」「月」「日」をそれぞれ置換します。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"年","-"),"月","-"),"日","")内側から順番に「年」→「月」→「日」を置換していき、「2026-06-08-」にならないよう最後の「日」は空文字で除去します。なお、ExcelのシリアルDate値として格納された日付にはTEXT関数のほうが適切で、=TEXT(A1,"yyyy-mm-dd")のように書式コードを指定する方法が最短です。SUBSTITUTEによる日付変換は、日付文字列が「テキスト型」で格納されているセルに対して使う技法です。
LEFT・MID関数と組み合わせて動的に置換する
SUBSTITUTE・REPLACEを他の文字列関数と組み合わせると、より複雑な加工が可能になります。例として、文字列の先頭1文字を大文字に変換する（英字の場合）パターンを見てみましょう。
=REPLACE(A1,1,1,UPPER(LEFT(A1,1)))REPLACE関数の第4引数「置換文字列」に、LEFT関数で取り出した先頭1文字をUPPER関数で大文字化したものを指定しています。このように、置換文字列の部分に別の関数を組み合わせることで「元の文字列の一部を加工した値で置き換える」複合操作が実現できます。
TRIM・CLEAN関数との使い分け
SUBSTITUTE関数はスペースの除去に使えますが、Excel には専用の文字列クリーニング関数もあります。用途に応じた使い分けを覚えておきましょう。
| 関数・数式 | 対象文字 | 動作 | 使うべき場面 |
|---|---|---|---|
| =SUBSTITUTE(A1,” “,””) | 半角スペース | すべての半角スペースを除去 | スペースを完全に削除したい場合 |
| =SUBSTITUTE(A1,” ”,””) | 全角スペース | すべての全角スペースを除去 | 全角スペースだけを除去したい場合 |
| =TRIM(A1) | 半角スペース | 先頭・末尾を除去、単語間は1スペースに統一 | スペースを整えたいが単語間の1スペースは残したい場合 |
| =CLEAN(A1) | 印刷不可能文字 | 改行・タブ・制御文字を除去 | CSVや外部システム取込データの制御文字を除去する場合 |
| =TRIM(CLEAN(A1)) | 両方 | 制御文字を除去してからスペースを整える | 外部データ取込後の総合クリーニング |
外部システムやCSVから取り込んだデータには、表示されない制御文字（改行コード・タブ文字・ゼロ幅スペースなど）が含まれていることがあります。こうしたデータはSUBSTITUTEで半角スペースを除去してもきれいにならない場合があります。CLEAN関数を先に適用して制御文字を除去し、その後TRIM・SUBSTITUTEで残ったスペースを処理するのが、外部データクリーニングの確実な手順です。
なお、TRIM関数は「先頭・末尾の余分なスペースを除去し、単語間の連続スペースを1つに統一する」という動作です。氏名の「山田 太郎」（スペース2つ）をTRIMにかけると「山田 太郎」（スペース1つ）になります。スペースを完全ゼロにしたい場合はSUBSTITUTE関数を使いましょう。
MOS Excel試験での出題傾向と対策
MOS Excel（一般・上級）では、文字列操作関数の問題が毎回出題されます。SUBSTITUTE・REPLACE関数に関する主な出題ポイントと対策を確認しましょう。
MOS試験頻出の出題パターン
| 出題パターン | 確認ポイント |
|---|---|
| 指定した文字列を別の文字列に置換する | SUBSTITUTEの4つの引数を正確に指定できるか |
| 特定の文字をセルから削除する | 置換文字列に””（空文字）を指定することを理解しているか |
| 固定位置から指定文字数を置換する | REPLACEの「開始位置」「文字数」の意味と書き方 |
| 文字数0で文字を挿入する | REPLACEの文字数引数を0にすると挿入になることを知っているか |
| 同じ文字が複数あるときの部分置換 | SUBSTITUTEの第4引数（置換対象番号）の使い方 |
試験本番での注意点
MOS試験ではExcel上で実際に操作を行うため、引数の順番を正確に記憶していることが重要です。SUBSTITUTE関数は「文字列・検索文字列・置換文字列・[置換対象]」の順番で、第4引数のみ省略可能です。REPLACE関数は「文字列・開始位置・文字数・置換文字列」の順番で、全4引数が必須です。
特に試験本番でミスしやすいのが、SUBSTITUTEとREPLACEをどちらを使うかの判断です。問題文に「〇〇という文字を××に変える」という表現があればSUBSTITUTE、「△文字目から□文字分を変える」という表現があればREPLACEを選ぶ、というフローを体に染み込ませておきましょう。また、SUBSTITUTE関数は大文字・小文字を区別するため、英字を含む文字列の置換では大文字・小文字を正確に指定することも試験での得点ポイントです。
練習問題：数式を自分で書けるか確認する
以下の状況に対応する数式を自分で記述できるか確認しましょう。
| 問題 | 模範解答 |
|---|---|
| A1の”株式会社”を”(株)”に置換する | =SUBSTITUTE(A1,”株式会社”,”(株)”) |
| A1からすべてのハイフン”-“を除去する | =SUBSTITUTE(A1,”-“,””) |
| A1の3文字目から4文字を”XXXX”に置換する | =REPLACE(A1,3,4,”XXXX”) |
| A1の6文字目に”/”を挿入する | =REPLACE(A1,6,0,”/”) |
| A1の2番目の”/”のみを”-“に置換する | =SUBSTITUTE(A1,”/”,”-“,2) |
| A1の半角スペースと全角スペースをどちらも除去する | =SUBSTITUTE(SUBSTITUTE(A1,” “,””),” ”,””) |
模範解答を確認したうえで、実際にExcelを開いてサンプルデータを入力し、上記の数式を1つずつ試して結果を確かめることが、MOS試験対策として最も効果的な方法です。SUBSTITUTE・REPLACEは関数ウィザードでも引数の確認ができますが、試験本番での時間節約のためにも直接セルに数式を入力できるレベルまで習熟しておきましょう。
まとめ：SUBSTITUTE・REPLACEで文字列置換の実務を効率化しよう
本記事では、ExcelのSUBSTITUTE関数とREPLACE関数について次の内容を解説しました。
- SUBSTITUTE：文字列の「内容」を基準に置換する関数。第4引数で何番目の出現を置換するか制御できる
- REPLACE：文字列の「位置と文字数」を基準に置換する関数。文字数0で挿入操作にもなる
- 置換文字列に””を指定することで不要な文字の「削除」として使える
- SUBSTITUTEの多段ネストで複数の文字を一度の数式で置換・除去できる
- TRIM関数は先頭・末尾・連続スペースの整理、CLEAN関数は制御文字の除去が得意
- 外部データのクリーニングにはTRIM(CLEAN(A1))の組み合わせが最も確実
- MOS試験ではSUBSTITUTE・REPLACEどちらを使うかの判断と引数の順番が頻出ポイント
SUBSTITUTE・REPLACE関数はExcelのデータ整備作業で日常的に使う基本関数です。元データを保持したまま「整形済みの文字列」を数式で生成できるため、データ品質の向上と業務効率化に直結します。これまで解説したLEFT・RIGHT・MID・LEN関数やCONCAT・TEXTJOIN関数と組み合わせることで、より複雑な文字列加工も1列の数式で解決できるようになります。まずは手元の顧客データや商品コードのクリーニング作業でSUBSTITUTE関数を試し、文字列置換の実力を着実に磨いていきましょう。
