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

Excelの初心者向け入門解説
公開日:2024-01-03 最終更新日:2024-01-03

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


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

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

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

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

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


SUBSTITUTEのネスト

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

エクセル Excel 複数置換 SUBSTITUTE

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

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

上記数式は、検索文字列をセル範囲にしていますのでスピルします。
SUBSTITUTE関数で指定できる「検索文字列」「置換文字列」は1種類だけになります。
文字列中の指定された文字をほかの文字に置き換えます。大文字と小文字は区別されます。SUBSTITUTE関数の書式 SUBSTITUTE(文字列,検索文字列,置換文字列[,置換対象]) 文字列 置き換える文字を含む文字列を指定します。
複数の文字列を検索して置換することはSUBSTITUTE関数では一度には出来ません。
「検索文字列」にセル範囲(または配列)を指定しても以下のようになってしまい上手くいきません。

エクセル Excel 複数置換 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関数は文字列操作ではとても便利で強力な関数です。
これを使う事で、複数文字列を取り除くことが簡単にできます。

エクセル Excel 複数置換 SUBSTITUTE

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

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

エクセル Excel 複数置換 SUBSTITUTE

=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関数はLAMBDAヘルパー関数(LAMBDAと一緒に使う)の一つです。配列にLAMBDAを適用して新しい値を作成することにより、配列内の各値を新しい値にマッピング(元配列に対して異なるデータを割り当て)して形成された配列を返します。MAP関数はLAMBDAヘルパー関数なので、LAMBDAについては別途習得して…
REDUCE関数(配列にLAMBDAを適用し累積値を返す)
・REDUCE関数の構文 ・REDUCEの基本動作 ・REDUCE関数の使用例と解説 ・初期値に配列を指定した場合 ・[初期値]を省略した時の動作
SCAN関数(配列にLAMBDAを適用し各中間値を返す)
・SCAN関数の構文 ・SCANの基本動作 ・SCAN関数の使用例と解説 ・[初期値]を省略した時の動作
BYROW関数(配列の行単位にLAMBDAを適用し列を集約)
・BYROW関数の構文 ・BYROWの基本動作 ・BYROW関数の使用例と解説
BYCOL関数(配列の列単位にLAMBDAを適用し行を集約)
・BYCOL関数の構文 ・BYCOLの基本動作 ・BYCOL関数の使用例と解説
MAKEARRAY関数(行数・列数で計算した配列を作成)
・MAKEARRAY関数の構文 ・MAKEARRAYの基本動作 ・MAKEARRAY関数の使用例と解説

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

エクセル Excel 複数置換 SUBSTITUTE

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

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

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

エクセル Excel 複数置換 SUBSTITUTE


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

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

エクセル Excel 複数置換 SUBSTITUTE

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)


新着記事NEW ・・・新着記事一覧を見る

無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)
イータ縮小ラムダ(eta reduced lambda)|エクセル入門(2023-11-20)
PIVOTBY関数(縦軸と横軸でグループ化して集計)|エクセル入門(2023-11-19)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.マクロとは?VBAとは?VBAでできること|VBA入門
10.セルのクリア(Clear,ClearContents)|VBA入門




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


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



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