SUBSTITUTE 完全ガイド：多段ネスト・LET・REDUCE で複数文字を一括置換
📖 目次
1 やりたいこと
セル内の「㈱」→「株式会社」、「（有）」→「有限会社」、全角スペース→半角スペースなど、複数の置換を一気に済ませたい。Ctrl+H の「検索と置換」では1つずつしか処理できず、元データを直接書き換えてしまうリスクもあります。関数なら元データを保持したまま、数式で一括変換できます。
2 SUBSTITUTE の基本構文
第4引数（省略可）：何番目に出現した文字を置換するか指定。省略するとすべて置換。
基本例：1つの置換
A1 に含まれる「㈱」をすべて「株式会社」に置き換える。元のセル（A1）は変更されない。
文字の「削除」にも使える
置換文字列を空文字 "" にすれば、該当文字を削除できる。電話番号のハイフン除去などに頻出。
動作イメージ
| A（元データ） | B（数式の結果） | |
| 1 | ㈱山田商事 | 株式会社山田商事 |
| 2 | ㈱鈴木工業 | 株式会社鈴木工業 |
| 3 | 佐藤建設㈱ | 佐藤建設株式会社 |
「㈱」が先頭にあっても末尾にあっても、すべて「株式会社」に置換される。
3 第4引数：N番目だけ置換する
第4引数を指定すると、N番目に出現した検索文字列だけを置換できます。省略するとすべてが対象です。
最初の（1番目の）ハイフンだけを削除。2番目以降のハイフンは残る。
活用例：区切り文字の選択的な置換
| A（元データ） | B（全て置換） | C（1番目だけ） | |
| 1 | 090-1234-5678 | 09012345678 | 0901234-5678 |
SUBSTITUTE の第4引数は「先頭から数えてN番目」しか指定できません。「最後のハイフンだけ」を置換するには、まず対象文字の出現回数を数えてからその数を第4引数に渡す工夫が必要です。
LEN(A1)-LEN(SUBSTITUTE(A1,"-","")) でハイフンの個数を算出し、それを第4引数に渡すことで最後のハイフンだけを削除。
4 多段ネスト（全バージョン対応）
置換したい文字が複数ある場合、SUBSTITUTE の結果をさらに SUBSTITUTE に渡す「多段ネスト」で対応します。
3段ネスト（3つの置換）
内側から順に実行される。①「㈱」→「株式会社」 ②「（有）」→「有限会社」 ③ 全角スペース→半角スペース
動作イメージ
| A（元データ） | B（結果） | |
| 1 | ㈱山田 商事 | 株式会社山田 商事 |
| 2 | （有）鈴木 工業 | 有限会社鈴木 工業 |
5段以上のネストになると、括弧の対応関係が人間にはほぼ読めなくなります。修正時に括弧を1つ消してしまうと数式全体が壊れるリスクも高まります。4段以上が必要な場合は LET や REDUCE の使用を強く推奨します。
5 LET で可読性を改善
LET関数で中間結果を変数に入れることで、ネストをフラットに書き直せます。各ステップが「何をしているか」一目でわかるようになります。
各ステップが独立した行に。変数名を「会社名正規化」「スペース統一」のように日本語にすればさらにわかりやすくなります。
5段の比較：ネスト版 vs LET版
❌ 5段ネスト（読めない）最後の行を途中の変数名（例：
s2）に変えるだけで、その段階までの置換結果を確認できます。ステップ実行のような感覚でデバッグできるのが LET の隠れた利点です。
6 REDUCE + LAMBDA で完全自動化
置換対象と置換後をテーブルとして管理し、REDUCE 関数で全行を順番に適用すれば、置換対象がいくら増えても数式の構造が変わりません。
置換テーブル（E列・F列）を用意する
| E | F | |
| 1 | 検索文字列 | 置換文字列 |
| 2 | ㈱ | 株式会社 |
| 3 | （有） | 有限会社 |
| 4 | （半角スペース） | |
| 5 | - | （空＝削除） |
| 6 | / | ／ |
数式（1本で全置換が完了）
REDUCE が E2:F6 の各行を順に処理。acc は累積結果（最初は A1 の値）、row は現在の行。INDEX(row,,1) で検索文字列、INDEX(row,,2) で置換文字列を取得して SUBSTITUTE に渡します。
新しい置換ルールが必要になったら、テーブルに1行追加して REDUCE の範囲を広げるだけ。数式自体には一切手を触れません。10個でも50個でも同じ数式で対応できます。
テーブル参照にすれば範囲の拡張も自動
E:F列をExcelテーブル（Ctrl+T）にしておけば、行追加時にテーブル範囲が自動拡張されます。
テーブル名「置換テーブル」を参照するので、行が追加されても数式の変更は不要。
MAP と組み合わせて複数行を一括処理
REDUCE は1つのセルに対する処理です。A列の全行に同じ置換を適用したい場合は MAP と組み合わせます。
A2:A100 の各セルに対して REDUCE による全置換が適用され、結果がスピルで展開されます。数式は1つのセルに書くだけ。
7 3つの手法 比較表
| 比較項目 | 多段ネスト | LET + SUBSTITUTE | REDUCE + LAMBDA |
|---|---|---|---|
| 可読性（3段まで） | まあまあ読める | 良い | 良い |
| 可読性（5段以上） | ほぼ読めない | 良い | 良い |
| 置換ルールの追加 | 数式を編集（括弧追加） | 数式を編集（行追加） | テーブルに1行追加するだけ |
| デバッグのしやすさ | 困難 | 変数名を差し替えるだけ | 中間確認がやや難しい |
| 数式の長さ | 置換数に比例して伸びる | 置換数に比例するが整理されている | 置換数に関係なく一定 |
| 置換ルールのメンテナンス | 数式内にハードコード | 数式内にハードコード | テーブルで外部管理 |
| 複数行への一括適用 | 各行にコピー | 各行にコピー | MAP併用で1数式スピル |
8 知っておくべき注意点
SUBSTITUTE の多段ネストでは、ある置換の結果が次の置換の入力になります。たとえば「AB」→「C」と「A」→「X」を同時に適用する場合、先に「A」→「X」が走ると「AB」が「XB」になり、「AB」→「C」が発動しなくなります。より具体的（長い文字列）な置換を先に配置してください。
SUBSTITUTE は大文字と小文字を区別します。「abc」を「xyz」に置換しても、「ABC」や「Abc」は変換されません。大文字小文字を無視して置換したい場合は、先に UPPER や LOWER で統一してから適用するか、Power Query の「値の置換」機能を検討してください。
SUBSTITUTE は「文字列の完全一致」でしか動作しません。
* や ? のようなワイルドカードには対応していません。パターンマッチが必要な場合は、Ctrl+H の「検索と置換」（ワイルドカード対応）や Power Query の「正規表現に近い処理」を検討してください。
=SUBSTITUTE(A1, "削除したい文字", "") で特定の文字を削除できます。これは CLEAN 関数（制御文字の除去）や TRIM 関数（余分なスペースの除去）ではカバーできない「任意の文字を狙い撃ちで消す」ための定番テクニックです。
9 SUBSTITUTE vs REPLACE vs CLEAN：使い分け
文字列を操作する関数は複数あり、混同されがちです。それぞれの守備範囲を整理しておきます。
| 関数 | 動作 | 指定方法 | 主な用途 |
|---|---|---|---|
| SUBSTITUTE | 特定の「文字列」を別の文字列に置換 | 文字列で指定（"㈱" → "株式会社"） | 特定の文字・単語の変換・削除 |
| REPLACE | 特定の「位置」の文字を別の文字列に置換 | 位置と文字数で指定（3文字目から2文字を"XX"に） | 固定フォーマットの一部差し替え |
| CLEAN | 印刷不可能な制御文字（ASCIIコード0〜31）を除去 | 引数はセルのみ（対象を指定できない） | 外部データ取込時の文字化け除去 |
| TRIM | 先頭・末尾のスペースと連続スペースを除去 | 引数はセルのみ | 余分なスペースの整理 |
SUBSTITUTE は「何を」（文字列の中身）で対象を特定します。REPLACE は「どこを」（位置）で特定します。「㈱をすべて株式会社に」なら SUBSTITUTE、「先頭3文字を***に」なら REPLACE です。
CLEAN では消えない厄介な文字
CLEAN が除去できるのは ASCII コード 0〜31 の制御文字だけです。ノーブレークスペース（CHAR(160)）やゼロ幅スペースなど、外部データに紛れ込みやすい「見えない文字」は CLEAN では消えません。こうした文字は SUBSTITUTE で狙い撃ちします。
まず CLEAN で制御文字を除去し、次に SUBSTITUTE でノーブレークスペース（CHAR(160)）を削除。Webからコピペしたデータのクレンジングに頻出するパターン。
10 実務パターン集
パターン① 会社名の正規化（㈱・㈲ → 正式名称）
パターン② 電話番号のハイフン・スペース除去
ハイフンとスペースの両方を除去して数字だけにする。
パターン③ セル内改行の除去
CHAR(10) は Alt+Enter で入力されるセル内改行。CLEAN でも除去できますが、SUBSTITUTE のほうが「何を消しているか」が明確です。
パターン④ 全角スペース→半角 + 全角英数字→半角（ASC併用）
全角スペースは ASC 関数では変換されないため、先に SUBSTITUTE で半角に置換してから ASC で英数字を半角化します。
パターン⑤ CSV用のダブルクォートエスケープ
セル内のダブルクォートを2つに増やし、全体をダブルクォートで囲む。CSV出力時のエスケープ処理。
パターン⑥ Webコピペデータのクレンジング（見えない文字の除去）
CLEAN で制御文字を除去 → SUBSTITUTE でノーブレークスペースを通常スペースに → TRIM で余分なスペースを整理。Web からコピペしたデータの定番クレンジング3点セット。
パターン⑦ LET で5段階の住所クレンジング
住所の表記揺れを段階的に正規化。各ステップの意図が明確なのでメンテナンスが容易。
パターン⑧ 特定文字の出現回数を数える（SUBSTITUTE応用）
SUBSTITUTEで対象文字を削除し、元の文字数との差で出現回数を算出。カンマの数を数えてCSVのフィールド数を確認する場面などに便利。
パターン⑨ 数値の桁区切り除去（文字列→数値変換の前処理）
「¥1,234,567」のような文字列からカンマと通貨記号を除去し、VALUE で数値に変換。外部システムから受け取った金額文字列の処理に頻出。
11 まとめ
| 場面 | 最適な手法 |
|---|---|
| 置換が1〜3個でシンプル | SUBSTITUTE 多段ネスト |
| 置換が4個以上で可読性を重視 | LET + SUBSTITUTE |
| 置換ルールが頻繁に追加・変更される | REDUCE + LAMBDA（テーブル管理） |
| 複数行に同じ置換を一括適用 | MAP + REDUCE + LAMBDA |
| 数百〜数千パターンの大規模クレンジング | Power Query |
① SUBSTITUTE の多段ネストは全バージョンで使えるが、4段以上は LET で書き直すのが実務的。
② 置換ルールが増える見込みがあるなら、最初からテーブル + REDUCE + LAMBDA で設計する。
③ 置換の順序は結果に影響する。具体的（長い文字列）なパターンから先に処理する。
④ SUBSTITUTE は大文字・小文字を区別し、ワイルドカードは使えない。
⑤ SUBSTITUTE は置換だけでなく、文字の削除（空文字への置換）や出現回数のカウントにも使える汎用的な関数。