本記事ではエクセルでSUBSTITUTE関数を使用して、一度に複数の文字を置換する方法をご紹介します。SUBSTITUTE関数で文字を置換する方法を応用して、本記事では、同じ1つの数式内で複数条件の文字やセルを削除または特定の文字列に置き換える方法を解説します。
結論: SUBSTITUTE関数で複数の文字を置換する方法
SUBSTITUTE関数で複数の文字を置換する方法は下記の2パターンがあります。[...]のようにカギカッコで囲まれた部分はご使用に合わせてご入力ください。
SUBSTITUTE関数を複数回使用し、置換したい文字列分繰り返す
こちらの方法では置換したい文字の数だけSUBSTITUTE関数をネストして使用する方法になります。
=SUBSTITUTE( SUBSTITUTE( ... SUBSTITUTE([テキスト], [検索文字列1], [置換文字列1]) ... , [検索文字列2], [置換文字列2]), [検索文字列3], [置換文字列3])
REDUCE関数とSUBSTITUTE関数を組み合わせて、1つの数式内で複数の文字を置換する
こちらの方法ではREDUCE関数を使用して、一度に置換したい文字をSUBSTITUTEで処理する方法となります。
=LET(init, [テキスト], old, [検索文字列 (範囲)], new, [置換文字列 (範囲)], REDUCE(init, SEQUENCE(ROWS(old)), LAMBDA(x, i, SUBSTITUTE(x, INDEX(old, i), INDEX(new, i)))))
(前提) SUBSTITUTE関数の解説
各方法の解説を始める前に、SUBSTITUTE関数の使用法を解説します。どちらの使用法の場合もSUBSTITUTE関数の理解を前提としていますので、わからない方のみご確認ください。すでにSUBSTITUTE関数を理解している方は読み飛ばしていただいて構いません。
SUBSTITUTE関数の解説概要
SUBSTITUTE関数は、Excelのセル内のテキストで指定した既存の文字列を新しい文字列に置き換えるための関数で、以下の構造で使用します。
SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])- 文字列: 文字列が含まれるセルまたは直接入力したテキスト
- 検索文字列: 置換を行いたい対象の文字列(置換前の文字列)
- 置換文字列: 既存の文字列を置き換える新しい文字列(置換後の文字列)
- 置換対象（オプション）: このオプションを使用すると、テキスト内の特定の位置のみで置換を行うことができます。例えば、3を指定した場合、3番目に現れる既存の文字列のみが置き換えられます。
SUBSTITUTE関数の具体例
具体例として、A2セルの”エクセル”を”ワード”に置換したい場合は下記の通りに使用します。
=SUBSTITUTE(A2, "エクセル", "ワード")
以上が、SUBSTITUTE関数を使用して、1つの文字列を置換する方法の解説になります。こちらをベースに置換対象が複数ある場合に、SUBSTITUTE関数を使用して一括で複数の文字を置換する方法を以下でご紹介します。
方法1：SUBSTITUTE関数をネストする(複数回使用する)
まず初めに、最も一般的なSUBSTITUTE関数をネストする(複数回使用する)方法をご紹介します。
例えば、置換したい文字が3個ある場合、1つ目のSUBSTITUTE関数を使用し、その外側に2つ目のSUBSTITUTE関数、さらに3つ目と何度もSUBSTITUTE関数を繰り返す方法になります。
こちらの式を使うメリットとデメリットは下記のとおりです
具体例付きの解説
以下に、SUBSTITUTE関数を複数回使用して、複数の文字列を一括で置換する方法を解説します。
計算結果と最終的な式
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, E2, F2), E3, F3), E4, F4), E5, F5), E6, F6), E7, F7)前提 (ご使用状況に合わせて入力値を更新してください)
- 置換を行う対象のテキスト
A2→ “あいうえおかきくけこ” - 置換前のテキスト
E2:E7→ {あ, い, う, か, き, く} - 置換後のテキスト
F2:F7→ {X, X, X, X, X, X}
ステップごとの解説
ステップ まず、1つ目のSUBSTITUTE関数を記述します。通常と同じ様に記述してください。複数ある置換対象のうち、上から順に指定していきます。
=SUBSTITUTE(A2, E2, F2)
ステップ 次に、2つ目のSUBSTITUTE関数を記述します。1つ目のSUBSTITUTEをカッコ内の1つ目の入力値として、下記のとおり記述します。
=SUBSTITUTE(SUBSTITUTE(A2, E2, F2), E3, F3)
ステップ 以降は同じ要領で必要な回数分SUBSTITUTEを繰り返します。今回のように置換対象の文字列が6個ある場合は下記のようになります。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, E2, F2), E3, F3), E4, F4), E5, F5), E6, F6), E7, F7)
以上が、SUBSTITUTE関数を複数回使用して、エクセル上で複数の文字列を一括で置換する方法の1つ目になります。直感的で分かりやすいものの、3個以上の置換対象がある場合煩雑で使いまわしにくい数式になってしまいますので、次の方法も検討してみてください。
方法2：REDUCE関数で複数回SUBTITUTEを適用し、一度に置換する
次に、REDUCE関数と組み合わせる方法をご紹介します。SUBSTITUTE関数を繰り返す方法と比べて理解が難しいものの、なれると非常に便利な方法です。
REDUCE関数の使い方(理解できなくてもOK)
こちらで今回使用するREDUCE関数の使用方法を説明します。ただし、今回は関数の挙動を理解していなくても使用できます。
- 構文:
REDUCE(初期値, 配列, 関数) - 各引数の説明:
引数 説明 初期値必須 計算開始時の値 配列必須 計算対象の配列 関数必須 適用する関数 - 参考: マイクロフト公式ドキュメント
具体例付きの解説
以下に、SUBSTITUTEを繰り返さずにREDUCE関数を使用して、複数の文字列を一括で置換する方法を解説します。
計算結果と最終的な式
=LET(init, A2, old, E2:E7, new, F2:F7, REDUCE(init, SEQUENCE(ROWS(old)), LAMBDA(x, i, SUBSTITUTE(x, INDEX(old, i), INDEX(new, i)))))前提 (ご使用状況に合わせて入力値を更新してください)
- 置換を行う対象のテキスト
A2→ “あいうえおかきくけこ” - 置換前のテキスト
E2:E7→ {あ, い, う, か, き, く} - 置換後のテキスト
F2:F7→ {X, X, X, X, X, X}
ステップごとの解説
ステップ LET関数と変数の定義
LET関数を使用していくつかの変数を定義します。
- init: A2→”あいうえおかきくけこ”
- old: E2:E7 →
あ, い, う, か, き, く - new: F2:F7 →
X, X, X, X, X, X
ステップ REDUCE関数の使用
REDUCE関数はA2の値から計算を開始します。
- 初期値:
A2→"あいうえおかきくけこ"
ステップ SEQUENCE関数
この関数を使って、1から始まりrowsの値(6)までの連続した数字の配列を生成します。
=SEQUENCE(6)
={1, 2, 3, 4, 5, 6}ステップ REDUCE関数とLAMBDA関数
LAMBDA関数はSEQUENCE関数によって生成された各値（1, 2, 3, 4, 5, 6）ごとに繰り返し呼び出されます。
第1回目
- x =
"あいうえおかきくけこ" - index =
1
=SUBSTITUTE("あいうえおかきくけこ", INDEX(old, 1), INDEX(new, 1))
=SUBSTITUTE("あいうえおかきくけこ", "あ", "X")
="Xいうえおかきくけこ"第2回目
- x =
"Xいうえおかきくけこ" - index =
2
=SUBSTITUTE("Xいうえおかきくけこ", INDEX(old, 2), INDEX(new, 2))
=SUBSTITUTE("Xいうえおかきくけこ", "い", "X")
="XXうえおかきくけこ"第3回目
- x =
"XXうえおかきくけこ" - index =
3
=SUBSTITUTE("XXうえおかきくけこ", INDEX(old, 3), INDEX(new, 3))
=SUBSTITUTE("XXうえおかきくけこ", "う", "X")
="XXXえおかきくけこ"このパターンで処理は進みます。
最終的な出力は "XXXえおXXXけこ" となります。
(応用編) 複数の文字を一括で置換するためのカスタム関数として登録する
名前付き範囲に下記の数式を登録することで、より簡略化して使用することができます。
=LAMBDA(text,old,new, REDUCE(text, SEQUENCE(ROWS(old)), LAMBDA(x,i, SUBSTITUTE(x, INDEX(old, i), INDEX(new, i)))))任意の関数名にて登録後、その関数を使用して複数の文字列を一括で置換することができるようになります。ここではSubAllという名前で登録しています。
ユーザー定義のカスタム関数を名前付き範囲に登録する方法は下記よりご確認ください。
おわりに
以上が、エクセルで複数の文字列を一括で置換することができるようになります。
一定の頻度で使用することがあると思いますので、是非マスターしてご自身のビジネス戦闘力を高めてください。
ご不明点や感想などがあります方はお気軽にコメントお待ちしております。
ご精読いただきありがとうございました。
コメント