さよならSUBSTITUTEネスト！TEXTSPLITとTEXTJOINで複数文字を一括置換する新テクニック
Excelを使っていると、「文字列の中にある複数の種類の文字や記号を、別の特定の文字にまとめて置き換えたい」という場面がよくありますよね。例えば、データ入力時にバラバラに使われてしまった全角スペース、半角スペース、タブ文字などを、すべて半角スペースに統一したい、といったケースです。
これまでは、置き換えたい文字の種類だけSUBSTITUTE関数を繰り返し使う、いわゆる「多重ネスト」で対応するのが一般的でした。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," "," "),CHAR(9)," "),"-"," ")この方法でも目的は達成できますが、置き換える文字の種類が増えるほど数式がどんどん長くなり、読みにくく、修正するのも大変でした(例は全角スペース,タブ文字,ハイフンを全角スペースに置き換える式)。
SUBSTITUTE関数のネストで一括置換
しかし、Excel 365の比較的新しい関数であるTEXTSPLIT関数とTEXTJOIN関数を組み合わせることで、この複数文字の一括置換をよりスマートにすることが可能です。この記事では、その具体的な計算式と、なぜそれで置換ができるのか、その仕組みを分かりやすく解説します。
複数文字列の一括置換計算式 基本形
早速ですが、その計算式の基本形をご紹介します。
=TEXTJOIN(置換後文字, FALSE, TEXTSPLIT(元の文字列, 置き換えたい文字リスト))置き換えたい文字リストの部分は、具体的にはHSTACK関数や配列定数{}を使って、置き換えたい文字（検索文字列）を複数指定します。
例えば、セルA1に入力された文字列の中から、「全角スペース」「半角スペース」「ハイフン-」「スラッシュ/」の4種類を、すべてアンダースコア_に置き換えたい場合は、以下のようになります。
=TEXTJOIN("_", FALSE, TEXTSPLIT(A1, HSTACK(" "," ", "-", "/")))あるいは、配列定数を使って
=TEXTJOIN("_", FALSE, TEXTSPLIT(A1, {" "," ", "-", "/"}))と書くこともできます。
なぜこの一見すると「分割して結合する」だけの式で、「置換」と同じ結果が得られるのでしょうか？その仕組みを見ていきましょう。
計算式の仕組み: 分解して再結合！
このテクニックは、2段階のステップで成り立っています。
完成例
ステップ1: TEXTSPLIT で「置き換えたい文字」を区切り文字にして分解
TEXTSPLIT(元の文字列, 置き換えたい文字リスト)の部分です。
元の文字列: 処理対象の文字列が入ったセル (例:A1)。置き換えたい文字リスト: ここでHSTACK(" ", "-", "/")や{" ", "-", "/"}のように、置き換えたい文字を複数指定します。TEXTSPLIT関数は、これらの文字のいずれかが見つかるたびに、元の文字列を分割します。- 重要なポイント: TEXTSPLIT関数の後ろの引数（特に
ignore_empty）を省略しています。これにより、ignore_emptyのデフォルト値であるFALSEが適用されます。FALSEの場合、連続する区切り文字や、文字列の先頭/末尾にある区切り文字によっても「空の要素（””）」が生成されます。これが、SUBSTITUTEネストの挙動（区切り文字がそれぞれ置換後文字に変わる）を再現するための鍵となります。
例えば、"ID - A / B C"という文字列をHSTACK(" ", "-", "/")で分割すると、(Excelの実装にもよりますが) {"ID", "", "", "A", "", "", "B", "C"}のような、空要素を含む配列に分解されます。
ステップ2: TEXTJOIN で「置換後文字」を区切り文字にして再結合
TEXTJOIN(置換後文字, FALSE, 分解結果)の部分です。
置換後文字: 最終的に置き換えたい文字を指定します (例:"_")。これが、TEXTJOIN関数で各要素を結合する際の「区切り文字」として使われます。FALSE: 2番目の引数ignore_emptyにFALSEを明示的に指定します。これも非常に重要です。これにより、ステップ1で生成された空の要素（””）も無視されずに、指定した「置換後文字」でちゃんと区切られて結合されます。分解結果: ステップ1のTEXTSPLIT関数が返した配列です。
例えば、ステップ1の結果{"ID", "", "", "A", "", "", "B", "C"}を、_を区切り文字にしてignore_empty=FALSEで結合すると、"ID___A___B_C"となります。元の文字列で区切り文字（スペース、ハイフン、スラッシュ）があった箇所が、見事に置換後文字（アンダースコア）に置き換わっていますね！
具体例で確認
実際にExcelで試してみましょう。セルA1に以下のデータがあるとします。
元データ (セル A1):
| FILE – NAME / 001 .xlsx |
この文字列中の「半角スペース」「ハイフン」「スラッシュ」「ドット」を、すべてアンダースコア_に置き換えたいとします。
置き換えたい文字のリストはHSTACK(" ", "-", "/", ".")または{" ", "-", "/", "."}となります。セルB1に以下の数式を入力します。
=TEXTJOIN("_", FALSE, TEXTSPLIT(A1, HSTACK(" ", "-", "/", ".")))実行結果 (セル B1):
FILE___NAME___001__xlsx期待通り、指定した4種類の文字がすべてアンダースコアに置き換わりました。
HSTACKの代わりに配列定数も利用可能
繰り返しになりますが、置き換えたい文字のリストを指定する部分は、HSTACK関数を使わずに、以下のように配列定数{ }で直接記述することも可能です。
=TEXTJOIN("_", FALSE, TEXTSPLIT(A1, {" ", "-", "/", "."}))書きやすい方、見やすい方を選んでください。
まとめ: このテクニックのメリット
今回ご紹介したTEXTSPLIT関数とTEXTJOIN関数を組み合わせる方法は、従来のSUBSTITUTE関数の多重ネストと比較して、以下のようなメリットがあります。
- 可読性の向上: 置き換えたい文字のリストが
HSTACK()や{}の中にまとまるため、数式の意図が掴みやすくなります。 - 保守性の向上: 置き換え対象の文字を追加したり削除したりするのが、ネスト構造を編集するよりも格段に楽になります。
- 数式が短くなる可能性: 置き換える文字の種類が多いほど、SUBSTITUTEネストよりも数式の文字数が少なくなる傾向があります。
このテクニックは、Microsoft 365版など、TEXTSPLIT関数とTEXTJOIN関数が利用できる比較的新しいバージョンのExcelで利用可能です。
これまでSUBSTITUTE関数のネストで苦労していた方は、ぜひこのよりスマートな一括置換の方法を活用してみてください。
コメント