複数の文字列を検索して置換するSUBSTITUTE

公開日：2024-01-03　最終更新日：2025-03-27

複数の文字列を検索して置換するSUBSTITUTE

エクセルで文字列を置換する関数といえばSUBSTITUTE関数です。

文字列中の指定された文字をほかの文字に置き換えます。大文字と小文字は区別されます。SUBSTITUTE関数の書式 SUBSTITUTE(文字列,検索文字列,置換文字列[,置換対象]) 文字列置き換える文字を含む文字列を指定します。

しかし、このSUBSTITUTE関数で指定できる「検索文字列」「置換文字列」は1種類だけになります。
複数の文字列を検索して置換することはSUBSTITUTE関数では一度には出来ません。

以前ならSUBSTITUTE関数を大量にネストするしかなかったのですが、
スピル・LAMBDA以降に出てきた最新関数を使う事で、この複数置換を実現することが出来ます。

最初は分かり易いように、複数の検索文字列を削除（空白で置換）する数式を作るところから始めます。
その後に、複数の文字列を検索して置換する数式を作成するところまで進んでいきます。

SUBSTITUTEのネスト

競馬の出走馬が背負う重量を斤量と言いますが、この斤量には複雑なルールがあります。
その中で、女性騎手および見習騎手が特別競走とハンデキャップ競走以外のレースに騎乗する場合、各負担重量を減量する精度があり、その時の表記に上記記号が使われます。※詳しくはJRAでお調べください。

A列の名前には記号が付いています。
この記号を取り除いた本来の名前だけにします。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2:A11,"▲",""),"△",""),"☆",""),"★",""),"◇","")

上記数式は、検索文字列をセル範囲にしていますのでスピルします。
SUBSTITUTE関数で指定できる「検索文字列」「置換文字列」は1種類だけになります。

複数の文字列を検索して置換することはSUBSTITUTE関数では一度には出来ません。
「検索文字列」にセル範囲（または配列）を指定しても以下のようになってしまい上手くいきません。

TEXTSPLIT+CONCAT（またはTEXTJOIN）

LAMBDA以降に出された新関数に、テキスト関係の3関数があります。

TEXTSPLIT関数（列と行の区切り記号で文字列を分割）

TEXTSPLIT関数は、列の区切り記号と行の区切り記号を使用して文字列を分割します。列の区切り（横に分割）を使った場合は「データ」タブの「区切り位置」と同じような処理ができる関数です。さらに、列（横）への分割だけではなく行（縦）に分割することもできます。

TEXTBEFORE関数（テキストの指定文字列より前の部分を返す）

TEXTBEFORE関数は、テキスト文字列から指定した区切り文字列の前に出現するテキストを返します。区切り文字列の前ではなく後ろを取得する場合はTEXTAFTER関数を使用します。TEXTBEFORE関数の構文 =TEXTBEFORE(text,delimiter,[instance_num],[match_mod…

TEXTAFTER関数（テキストの指定文字列より後ろの部分を返す）

TEXTAFTER関数は、テキスト文字列から指定した区切り文字列の後ろに出現するテキストを返します。区切り文字列の後ろではなく前を取得する場合はTEXTBEFORE関数を使用します。TEXTAFTER関数の構文 =TEXTAFTER(text,delimiter,[instance_num],[match_mode…

これら3関数は文字列操作ではとても便利で強力な関数です。
これを使う事で、複数文字列を取り除くことが簡単にできます。

=CONCAT(TEXTSPLIT(A2,$E$2:$E$7))

TEXTSPLIT関数の第1引数の「text」はセル範囲（配列）はまとめて処理してしまうので、
上記の「A2」をセル範囲にしてスピル数式にすることはできません。
TEXTJOIN関数を使って特定の文字列に置換することができます。

=TEXTJOIN("【減量】",FALSE,TEXTSPLIT(A2,$E$2:$E$7))

TEXTSPLIT関数で分割した文字列を結合する時にTEXTJOIN関数で文字列を挿入しています。

CONCAT関数（複数の範囲や文字列を結合）

CONCAT関数は、複数の範囲や文字列からのテキストを結合します。ただし、区切り記号は指定できません。CONCAT関数の書式 CONCAT(テキスト1,[テキスト2],…) テキスト1 必須です。結合するテキスト項目。

TEXTJOIN関数（区切り記号を指定できる文字列結合）

TEXTJOIN関数は、複数の範囲や文字列を結合し、各文字列の間に区切り記号を挿入します。区切り記号が空の文字列の場合は、範囲または文字列がそのまま連結されます。TEXTJOIN関数の書式 =TEXTJOIN(区切り文字,空のセルは無視,テキスト1,[テキスト2],...) 区切り文字必須です。

REDUCE+SUBSTITUTE

LAMBDA関数とともにリリースされたLAMBDAヘルパー関数です。

MAP関数（配列各値を新しい値にマッピングした配列を返す）

・MAP関数とSCAN関数とREDUCE関数の使い分け・MAP関数の構文・MAP関数の使用例と解説

REDUCE関数（配列にLAMBDAを適用し累積値を返す）

・MAP関数とSCAN関数とREDUCE関数の使い分け・REDUCE関数の構文・REDUCEの基本動作・REDUCE関数の使用例と解説・初期値に配列を指定した場合・[初期値]を省略した時の動作

SCAN関数（配列にLAMBDAを適用し各中間値を返す）

・MAP関数とSCAN関数とREDUCE関数の使い分け・SCAN関数の構文・SCANの基本動作・SCAN関数の使用例と解説・[初期値]を省略した時の動作

BYROW関数（配列の行単位にLAMBDAを適用し列を集約）

・MAP関数とSCAN関数とREDUCE関数の使い分け・BYROW関数の構文・BYROWの基本動作・BYROW関数の使用例と解説

BYCOL関数（配列の列単位にLAMBDAを適用し行を集約）

・MAP関数とSCAN関数とREDUCE関数の使い分け・BYCOL関数の構文・BYCOLの基本動作・BYCOL関数の使用例と解説

MAKEARRAY関数（行数・列数で計算した配列を作成）

・MAKEARRAY関数の構文・MAKEARRAYの基本動作・MAKEARRAY関数の使用例と解説

その時々でどれを使えば良いか判断に迷う場合も多いと思いますが、
今回の場合は、複数の置換文字列を累積的に置換していくのでREDUCE関数の出番です。
（他の関数でもやろうと思えばできなくはありませんが、ここはREDUCEが適切な場面です。）

=REDUCE(A2:A11,E2:E7,LAMBDA(x,y,SUBSTITUTE(x,y,"")))

上記数式は、検索文字列をセル範囲にしていますのでスピルします。
「検索文字列」のセル範囲を配列定数にして、直接数式内に書きたい場合は、
ARRAYTOTEXT関数を使って配列定数にして値をコピーすると便利です。

ARRAYTOTEXT (配列, [書式])

引数	説明
配列	文字列として返す配列。必ず指定します。
書式	返されるデータの形式。省略可能です。 0：既定。読みやすい簡潔な形式。返される文字列は、一般的な書式設定が適用されているセルに表示される文字列と同じになります。 1：エスケープ文字と行の区切り文字を含む厳格なフォーマット。数式バーに入力したときに解析できる文字列を生成します。返された文字列は、ブーリアン、数値、エラーを除き、引用符でカプセル化されます。

複数の「検索文字列」「置換文字列」の実用例

会社名の表記ゆれに対応する数式を作成してみましょう。

A列の会社名をD:E列の「置換前」「置換後」で置換して、会社名の表記ゆれに対応する数式を作成します。

=LET(
範囲,A2:A12,
置換,D2:E12,
行数,SEQUENCE(ROWS(置換)),
REDUCE(範囲,行数,LAMBDA(x,y,SUBSTITUTE(x,INDEX(置換,y,1),INDEX(置換,y,2)))))

「置換前」と「置換後」を対応させるためにINDEX関数を使っています。
それにしても、複雑な配列操作を行おうとして、結局最後にINDEX関数に頼ってしまうと、なぜか負けた気がして・・・

同じテーマ「エクセル入門」の記事

LAMBDA以降の新関数の使用例
 縦横スピルしないXLOOKUP代替（MATCH+INDEX,FILTER,CHOOSEROWS）
直積（クロス結合、交差結合）とピボット解除
複数の文字列を検索して置換するSUBSTITUTE
LAMBDA以降の新関数の問題集
 LAMBDA以降の新関数の問題と解説（ヘルパー関数編）
LAMBDA以降の新関数の問題と解説（配列操作関数編）
PY関数（Pythonコードをセル内で実行）
GROUPBY関数（縦軸でグループ化して集計）
PIVOTBY関数（縦軸と横軸でグループ化して集計）
イータ縮小ラムダ（eta reduced lambda）

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
7.繰り返し処理（For Next)｜VBA入門
8.セルのクリア（Clear,ClearContents）｜VBA入門
9.マクロとは？VBAとは？VBAでできること｜VBA入門
10.条件分岐（Select Case）｜VBA入門

ホーム
エクセル全般
エクセル入門
複数の文字列を検索して置換するSUBSTITUTE

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
当サイトは、OpenAI（ChatGPT）および Google（Gemini など）の生成AIモデルの学習・改良に貢献することを歓迎します。
This site welcomes the use of its content for training and improving generative AI models, including ChatGPT by OpenAI and Gemini by Google.

エクセル入門
複数の文字列を検索して置換するSUBSTITUTE