１セル内の複数データと表を一致させる

質問

非常に難儀しております。
ご存知の方お教えください。

前提として表にそれぞれにコードがついています。
（｜は別セルを表現）
肉まん　　　　　｜　aa01
あんまん　　　　｜　bb01
カスタードマン　｜　cc01
ピザマン　　　　｜　ee01

そして１つのセル内に複数のデータをランダムで
入力します。（最低でも二文字はあります）

こんな感じ↓
１）肉まん・あんまん・カスタードマン・ピザマン
２）カスタードマン・あんまん

この時、結果を
１）aa01・bb01・cc01・ee01
２）cc01・bb01
と自動計算で表示することは可能でしょうか。
できれば計算式を含めてお教え願えませんでしょうか。
よろしくおねがいします。

回答No.1

cafe_au_lait · ベストアンサー

ユーザー定義関数を使います。

Alt+F11を押してVisual Basic Editorを開きます。
挿入→標準モジュールを選択し、出てきた画面に以下のコードを入力します。

Function SubstituteAll(ByVal OrStr As String, ByVal ChRange As Range) As String

Dim i As Variant
Dim j As Integer

i = ChRange.Value

For j = 1 To UBound(i, 1)
OrStr = Replace(OrStr, i(j, 1), i(j, 2))
Next j
SubstituteAll = OrStr

End Function

使い方
元の文字列がA5に、置換したい組み合わせがA1:B4にあるとして、
=substituteall(A5,A1:B4)
と入力します。

お礼

完全一致の説明が不完全ですので、
お礼のところに追加させていただきます。
例の表の中に”肉まんあんまん”というものがあり、
教えていただいた方法だと
zz01とはならずに、aa01bb01と表示されてしまいます。

肉まん　　　　　｜　aa01
あんまん　　　　｜　bb01
肉まんあんまん　｜　zz01

お手数ですがよろしくお願いします。

補足

すごいです。ありがとうございます。こんな
設定ができるとは知りませんでした…。
もし可能なら完全一致のケースも教えていただけませんでしょうか？
よろしくお願いします

回答No.2

cafe_au_lait

データの区切り記号は1種類のみでしょうか？

Function Split_Lookup(ByVal OrStr As String, ByVal ChRange As Range, ByVal Delim As String) As String '元の文字列、変換リスト、区切り記号

Dim i As Integer
Dim ArStr As Variant

ArStr = Split(OrStr, Delim) '区切り記号で分ける

For i = 0 To UBound(ArStr)
On Error Resume Next '変換リストにない場合に無視する
ArStr(i) = Application.WorksheetFunction _
.VLookup(ArStr(i), ChRange, 2, False) 'VLOOKUP関数で変換
On Error GoTo 0
Split_Lookup = Split_Lookup & ArStr(i) '変換したものを戻り値に追加
If i <> UBound(ArStr) Then
Split_Lookup = Split_Lookup & Delim '区切り記号を挟む
End If
Next i

End Function

=Split_Lookup(A5,A1:B4,"・")

まず3番目の引数"・"で分けて、それぞれVLOOKUP関数で変換し、最後にもう一度"・"でつないでいます。要するに、関数を作らなくても手作業で置換できるわけですが・・・。まあ、データ量次第でしょうか。