こんにちは。すごい改善のAitoです。

この記事ではExcelの関数による置換削除について解説します。 Office 365の方以外は一部動作しない関数がございますが、ご了承ください。

Excelで「置換」と聞くと何を思い浮かべますか？Ctrl+Hで置換ウィンドウの呼び出しでしょうか。それともSUBSTITUTE関数でしょうか。REPLACE関数でしょうか。 色々あると思いますが、関数を使うと関数のコピーで一括適用できたり、データを入れ替えても自動で反映されたりと何かと便利ですよね。 この置換機能は特定の文字を削除するのにも大変有効です。 ここではExcel関数で置換をする際に最も使われるSUBSTITUTE関数を使って、指定の文字を削除する方法について考えます。

例えば以下のような場合ですね。

ex) 「リンゴとみかんではりんごの方が美味しい」という文字列から「みかん」という文字列を削除

A1セルに「リンゴみかんは美味しい」という文字列が入力されているとします。A2に「みかん」を削除した文字列を入れたいときはどのように数式を書けばいいでしょうか。

=SUBSTITUTE(A1,"みかん","")

で出来ますよね！では先ほどの文字列から「りんご」と「みかん」を削除したい場合はどうでしょうか。上記の式を改変してみましょう。SUBSTITUTEを入れ子にするとSUBSTITUTEで置換された文字列を置換対象文字列にすることができますね。

=SUBSTITUTE(SUBSTITUTE(A1,"みかん",""),"りんご","")

さて、確かに置換はできたのですが、数が増えた場合はどうかいたらよいでしょうか。

例えば、半角の空白と全角の空白とハイフンとアンダーバーと...etcと増えていった場合です。この場合はSUBSTITUTEを入れ子にすると3つを超えたあたりからややこしくなってきますし、Excelの仕様上64個以上の入れ子ができません。つまり書きにくい上に、個数の面で条件が限られるということです。

では条件が増えた際に、簡単に削除するにはどうしたらよいのでしょうか。最新版のOffice 365版Excelであれば、簡単に実現できます。

使うのは以下の関数です。

LET関数、SCAN関数、LAMBDA関数、SUBSTITUTE関数、TAKE関数

各関数に関してはExcelの神髄というサイトで解説していますのでそちらをご覧ください。

https://excel-ubara.com/excel1/EXCEL636.html

https://excel-ubara.com/excel1/EXCEL686.html

https://excel-ubara.com/excel1/EXCEL674.html

さて、例を決めましょうか。今回は消去ワードを「Excel」「Word」「Power Point」にします。

置換する文字列は、「ExcelもWordもPower Pointもとても便利ですよね！あなたもそう思いませんか？Excelに関してわからないことがあれば、すごい改善にご相談ください！」としておきましょう。

では、これを置換していきましょう。

=LET(
削除, SCAN(A7,A2:A4,LAMBDA(テキスト,ワード,SUBSTITUTE(テキスト,ワード,""))),
TAKE(削除,-1,1)
)

と入れてみましょう。「もももとても便利ですよね！あなたもそう思いませんか？に関してわからないことがあれば、すごい改善にご相談ください！」と置換できているはずです。

簡単に数式の解説をします。変数「削除」に、SCAN関数でスピルされた範囲をセットします。

SCAN関数では置換文字列を初期値にセットして、その文字列をA2:A4の範囲、すなわち「Excel」「Word」「Power Point」で順番に置換していってその結果をスピルとして順番に返します。つまり、スピル1行目はExcelが消え、2行目はExcelとWordが消え、3行目はExcelとWordとPower Pointが消えます。これが変数「削除」に格納されるわけですね。

次にスピルされた配列変数「削除」の最後の部分を取得すればすべて置換削除された文字列が取得できるというわけです。この最後を取得するために使うのがTAKE関数です。今回消去ワードは縦に並んでいるので、スピルも縦になります。ですので、最後を取得する際には行を-1、列1にします。負の数が出てくるのは、-1とすると行(もしくは列)を最後から取得することができるからなのです。

今回の書き方を抽象化すると、置換用の条件セル範囲が縦に並んでいるなら

=LET(
削除, SCAN([置換対象文字列],[置換用の条件セル範囲],LAMBDA(テキスト,ワード,SUBSTITUTE(テキスト,ワード,""))),
TAKE(削除,-1,1)
)

置換用の条件セル範囲が横に並んでいるなら

=LET(
削除, SCAN([置換対象文字列],[置換用の条件セル範囲],LAMBDA(テキスト,ワード,SUBSTITUTE(テキスト,ワード,""))),
TAKE(削除,1,-1)
)

と書くことができます。[]がついているところのみ変更してください。変更後は[]は必要ありません。これだけが正解というわけではなく、LAMBDA関数のヘルパー関数と呼ばれる関数を使うことで他にも同じ動作を実現できます。

VBAや作業列を作っても同様のことができますが、作業列を使わず短く書くことができ、VBAも使っていないので最新の環境においては便利かなと思います。関数型プログラミング言語に似ているのでそちらを調べてみてもいいかもしれませんね。

※ご指摘を頂いたので追記しておきます。

今回SCAN関数を選択したのは途中経過を出力できたり、ちょっとした操作をしやすくするためです。

最も簡単に置換結果を取得したい場合は、REDUCE関数を使うことをお勧めします。

以下に使用例を示します。こちらは縦横を考えずに置換用の条件セル範囲を指定するだけで置換ができます。

=REDUCE([置換対象文字列],[置換用の条件セル範囲],LAMBDA(テキスト,ワード,SUBSTITUTE(テキスト,ワード,"")))

SCAN関数は処理結果が積みあがっていくのに対し、REDUCE関数は最終結果のみを取得します。ご自身の作業条件に合わせて使い分けてください。

ご参考までに。

それでは良いExcelライフを！

Aito（株式会社すごい改善）

• この著者の最近の記事
• SUMIFSの条件判定をOR条件にする方法
• ExcelとWordを使って超お手軽単語頻度分析
• Excelで簡易的にWAVファイルから周波数分析をしてみよう
• この執筆者のすべての記事を見る

• 『たった1日で即戦力になるExcelの教科書』執筆陣が自ら直接指導。
  実務直結・一日集中・受講後無期限サポート付きのExcelセミナー