ExcelのSUBSTITUTE関数について教えてください。

セルA列に1000個のワードがあり、この中から変換して置き換えたい文字が100個ある場合に、

SUBSTITUTEで数式を組み立てているのですが、

ネストが64までということで、シート1に“変換前の文字”と“変換後の文字”を64個、

シート2に同様に36個用意して、セルA列を対象にそれぞれ変換していき、

シート1で変換できた文字と、シート2とで変換できた文字を抜粋して、

シート3で合わせて1000個のワードを変換完了、とするしか方法はありませんか？

簡潔に言いますと、1000個のワードに対して、100個の異なる文字の置換が

一度にできる関数の組み立て方があれば知りたいです。

No.6 回答者： evollecxe 回答日時： 2019/11/09 17:40

連投すみません。

。
No.5は参考にしないでくださいね。
同じこともっとシンプルでできるのに、、
以上、失礼しました。

No.5 回答者： evollecxe 回答日時： 2019/11/09 17:26

No付きの変換テーブルを用意していただく前提で、

sumproductで一致したNo返して、そのNoでvlookupして、変換というのはどうでしょうか。
ご要望通りかわかりませんがお試しください。

=IFERROR(VLOOKUP(SUMPRODUCT((A3=$E$3:$E$7)*$D$3:$D$7),$D$3:$F$7,3,FALSE),A3)

No.4 回答者： GooUserラック 回答日時： 2019/11/07 03:50

ユーザー定義関数（ReplaceD）を作ってみました。

普通のシート関数のように「=ReplaceD(A1)」のような使い方になります。
一度変換テーブルシートを配列に読み込んで使用していますので少しは早くなっていると思います。

インストール方法
① 変換したいデータが有るブックにシートを追加してシート名を「変換テーブル」に変更する。
② 追加したシートのＡ列に「変換前の文字」Ｂ列に「変換後の文字」を列挙します。
③「Public Const Str_戻シート名 As String = "Sheet1"」の「Sheet1」を実際に存在するシート名に変更して下さい（変換したいデータが有るシート名にした方が便利です）
④ 点線以下のコードを図のそれぞれの赤枠で囲んだところをダブルクリックしたところに書き込みます。
⑤ マクロ有効ブック（○○○○.xlsm）で保存して閉じます。
⑥ 次回マクロを有効にして開くと「ReplaceD」が使えるようになっています。

-------------------------------------------------------------------------------------------------------------
☆「変換テーブル」へ

Private Sub Worksheet_Deactivate()
　Call シート⇒配列
End Sub

-------------------------------------------------------------------------------------------------------------
☆「ThisWorkbook」へ

Private Sub Workbook_Open()
　Sheets(Str_変換シート名).Select
　Sheets(Str_戻シート名).Select
End Sub

-------------------------------------------------------------------------------------------------------------
☆「Module1」へ

Public Const Str_戻シート名 As String = "Sheet1"
Public Const Str_変換シート名 As String = "変換テーブル"
Public Var_変換テーブル As Variant
Public Lng_登録終 As Long

Sub シート⇒配列()
　Application.EnableEvents = False
　　Sheets(Str_変換シート名).Select
　　Lng_登録終 = Cells(Rows.Count, 1).End(xlUp).Row
　　Var_変換テーブル = Range(Cells(1, 1), Cells(Lng_登録終, 2))
　　Sheets(Str_戻シート名).Select
　Application.EnableEvents = True
End Sub

Function ReplaceD(文字列 As String) As String
Dim Lng_位置 As Long
　ReplaceD = 文字列
　For Lng_位置 = 1 To Lng_登録終
　　ReplaceD = Replace(ReplaceD, Var_変換テーブル(Lng_位置, 1), Var_変換テーブル(Lng_位置, 2))
　Next
End Function
-------------------------------------------------------------------------------------------------------------

No.3 回答者： fujillin 回答日時： 2019/11/05 09:24

こんにちは

＞関数の組み立て方があれば知りたいです。
エクセルに組込みの関数では難しそうなので、ユーザー定義関数にしてみました。

準備として、VBAの標準モジュールに下記の関数をコピペしておきます。
そのうえで、置き換えた結果を表示したいセルに
　=exchange(元の文字列、置換文字列一覧表）
のようにして使います。

添付の例では、仮に、A列に元となる文字列が並んでおり、D1:E6の範囲が置換する文字列の一覧としています。
一覧は、D列の文字が存在すれば、E列の文字に置換するという意味の表になります。

結果を示したいB列には、B1セルに
　=exchange(A1,D$1:E$6)
の関数式を入力して、下方にフィルコピーしてあります。



標準モジュールに記入したユーザー定義関数は、以下の通りです。
Function exchange(ByVal s As String, ByRef r As Range) As String
　Dim i As Long
　exchange = s
　If r.Columns.Count < 2 Then Exit Function
　For i = 1 To r.Rows.Count
　　exchange = Replace(exchange, r.Cells(i, 1), r.Cells(i, 2))
　Next i
End Function

No.2 回答者： tom04 回答日時： 2019/11/04 20:43

こんばんは！

VBAでの一例です。
文章だけではどのような配置になっているのか判らないので、
Sheet1のA列に元の文言が羅列してあるとします。
そしてSheet2のA列1行目から検索する文字列・B1セル以降に置換後の文字列の表を作成しておきます。

操作する前に一手間掛けます。
Sheet2のC1セルに
=LEN(A1)
という数式を入れフィルハンドルでダブルクリック！
そしてA1～C列最終行までを範囲指定し、C列をキーに降順で並び替えをしておきます。
（1行目から順に置換しますので、文字数の多い順に置換をしないとお望みの結果にならないと思います）

その下準備ができた上で
Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面を閉じて）マクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() '//この行から//
　Dim i As Long, wS As Worksheet
　　Set wS = Worksheets("Sheet2")
　　　For i = 1 To wS.Cells(Rows.Count, "A").End(xlUp).Row
　　　　Worksheets("Sheet1").Range("A:A").Replace what:=wS.Cells(i, "A"), replacement:=wS.Cells(i, "B"), lookat:=xlPart
　　　Next i
End Sub '//この行まで//

※　一旦マクロを実行すると元に戻せませんので
Sheet1のA列は別の列にコピー＆ペーストしておいてマクロを試してみてください。m(_ _)m

No.1 回答者： ぐーるぐる 回答日時： 2019/11/04 15:28

置き替えのデータ一覧を作っておいて、Vlookupで、1000件の隣の列に置換データを入れてあげたら？