置換元と置換先は複数の組になっているものとします。
- SUBSTITUTE関数をネストするか、REDUCE関数と併用することで複数の置き換えを同時に行うことができます。
SUBSTITUTE関数をネストする方法
単純明快ですが置き換えの条件が増えるほど式が長くなっていく欠点がある方法です。
画像ではB3セルのテキストに対し、D,E列に記された3つの置き換え条件による置き換えを実行しています。
G3セル
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,D3,E3),D4,E4),D5,E5)
SUBSTITUTE関数を3重にネスト（入れ子）しています。内側の置き換え結果が外側に引き渡されていき置き換えが完成します。
理屈はわかりやすいものの本質的に置き換えを1つ1つ繰り返しているのと変わりがないのが問題で、置き換え条件が10組とかになると式が非常に長くなり条件の追加や削除に対する変更も面倒になります。
なお、式を下にコピーしていくとD,E列に対する参照がずれるので、次の画像ではARRAYFORMULAで対応してみました。
条件が増えるほど絶対参照の設定も面倒になってくるのでこちらの方法の方がよいでしょう。
G3セル
=ARRAYFORMULA(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3:B8,D3,E3),D4,E4),D5,E5))
SUBSTITUTE関数とREDUCE関数を併用する方法
ネストに代わる効果をもたらすREDUCE関数を使う方法です。
やや難しいですが置き換え条件が増えても式が長くならないメリットがあります。
G3セル
=REDUCE(B3,D3:D5, LAMBDA(x,a,SUBSTITUTE(x,a,XLOOKUP(a,D3:D5,E3:E5))) )
対象テキストであるB3をxとし、また、3つの置換元文字列であるD3:D5をaとしています。
aのそれぞれについてSUBSTITUTE関数により置き換えを実行していきますが、このとき対応する置換先文字列をXLOOKUP関数によりE3:E5から参照しています。
そしてREDUCE関数のはたらきにより、置き換えられたテキストが次の置き換え前のテキストとして扱われるのでSUBSTITUTE関数をネストするのと同じ結果になります。
やや難しく構文も複雑ですが、置き換え条件がいくら増えても式を追加していく必要がないのがメリットです。
次はARRAYFORMULAでG4セル以降の出力を行ったところです。
G3セル
=ARRAYFORMULA(REDUCE(B3:B8,D3:D5, LAMBDA(x,a,SUBSTITUTE(x,a,XLOOKUP(a,D3:D5,E3:E5))) ))
なお本記事ではSUBSTITUTE関数を使いましたが、代わりにREGEXREPLACE関数を利用することで、より高度な置き換えも可能になります。
