【Excel関数】特定の文字を別表のリストの文字に変換したい

A2の文字を別表のリストを参照して、A4の文字に変換したい。
※別表のリストはレモン以降もあり

また、変換する際A2で「//」になっている部分は「/」に、最後に「/」がついていたら削除して
A4のように変換して表示したいです。

どのような数式にしたらよろしいでしょうか？
教えてください。

質問者からの補足コメント

• 

  お礼で無事解決したとお伝えしたのですが、追加でご教授願います。
  
  (COLUMN(A1:Z1))
  →この部分なのですが、どのような意味か教えていただいてもよろしいでしょうか？

  No.3の回答に寄せられた補足コメントです。 補足日時：2022/10/10 18:52

No.4 ベストアンサー 回答者： Belltreeriver 回答日時： 2022/10/10 21:17

意味を説明するのは数式を考えるより難しいです。

解らなかったら悪しからず。

COLUMN(A1:Z1)は1から26の配列を生成します。
従って文字列の単語が26個まで対応することになります。
実態に合わせて変更してください。
＜例＞30個ならCOLUMN(A1:AD1)

数式の概要は
TRIM(MID(SUBSTITUTE(SUBSTITUTE("♪"&SUBSTITUTE(A2,"//","♪"),"/",""),"♪",REPT(" ",100)),(COLUMN(A1:Z1))*100,100))で
りんご_いちご_…レモンという単語の文字間が100個の半角ブランクで繋がった文字列（_は半角ブランク100個を表す）を作成。

TRIM(MID(上記の文字列,1*100,100))→りんご
（100文字目から100文字を取得し、前後のブランクを削除）
TRIM(MID(上記の文字列,2*100,100))→いちご
（200文字目から100文字を取得し、前後のブランクを削除）
　　　　　　　：
TRIM(MID(上記の文字列,6*100,100))→レモン
TRIM(MID(上記の文字列,7*100,100))→""
　　　　　　　：
TRIM(MID(上記の文字列,26*100,100))→""
で次の26個の単語配列を生成。
{"りんご","いちご","ぶどう","ばなな","めろん","レモン","","",…}

MATCH関数で、この配列を検索値としてD列（検査範囲）を検査し、返った数値（配列）をLOOKUPの検査値としてC列（検査範囲）E列（対応範囲）
し、変換文字を配列取得。MATCHでエラーになる場合（置換表に文字が無い）は26個の単語配列から元の単語を配列取得。
{"Apple","Strawberry","Grape","Banana","Melon","Lemon","","",…}

TEXTJOINで配列の文字を編集

この回答へのお礼

おはようございます。
とてもわかりやすくご説明いただき感謝いたします。
数式の内容を理解いたしました。
今回こそ無事に解決いたしました。

何度もお手数をおかけして申し訳ございませんでした。
ありがとうございました！

お礼日時：2022/10/11 09:39

No.3 回答者： Belltreeriver 回答日時： 2022/10/10 07:08

№2です。

置換表にヒットしない文字は元の文字を残す配列数式になります。
置換表は図のように最左に№（連番）を追加してください。
ローテクな配列数式なので大変長いです。
A4=TEXTJOIN("/",,IFERROR(LOOKUP(MATCH(TRIM(MID(SUBSTITUTE(SUBSTITUTE("♪"&SUBSTITUTE(A2,"//","♪"),"/",""),"♪",REPT(" ",100)),(COLUMN(A1:Z1))*100,100)),D2:D8,0),C2:C8,E2:E8),TRIM(MID(SUBSTITUTE(SUBSTITUTE("♪"&SUBSTITUTE(A2,"//","♪"),"/",""),"♪",REPT(" ",100)),(COLUMN(A1:Z1))*100,100))))

この回答へのお礼

№３　様
こんにちわ。
ご回答ありがとうございます。

ご提示いただいた数式で無事解決いたしました。
ご丁寧に教えてくださり、ありがとうございました！

お礼日時：2022/10/10 18:23

No.2 回答者： Belltreeriver 回答日時： 2022/10/10 00:06

ローテクな配列数式でEXCEL2019以降で対応します。

置換表はB列で昇順にします。
※置換表にヒットしない文字は文字列から除外されます。
A4=TEXTJOIN("/",,IF(ISERROR(MATCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),
COLUMN(A1:Z1)),$B$2:$B$8,0)),"",IFERROR(LOOKUP(MID(A2,
ROW(INDIRECT("1:"&LEN(A2))),COLUMN(A1:Z1)),$B$2:$B$8,$C$2:$C$8),"")))

Ctr+Shift+Enterで数式確定入力

No.1 回答者： Qchan1962 回答日時： 2022/10/09 20:10

こんばんは

各文字列を探し置き換え、つなげる・・TEXTSPLIT・MATCH・INDEXを使っても
1セルでやるのは・・どうだろう？無理っぽいけど
文字の探し置き換えが無くただ決まった文字を置き換えるだけなら
例えば、
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"//","|"),"/",""),"|","/")
とか・・関数は良く解らないので　

ユーザー定義関数でやっちゃうのはどうでしょう
かのん2222様は使い方をご存知かと思いますので登録詳細は割愛

Function VlpRep(rVip As Range, rTrg As Range, ix As Integer, n As Integer, _
Wt As String, Rp As String, k As Integer) As String
'VlpRep(vLookup範囲,計算対象セル,vLookup抽出列index,検索方法,置換文字,置換後文字,右削除文字数)
Dim arr As Variant
Dim strTrg As String
Dim i As Long, buf As String
strTrg = Left(rTrg.Text, Len(rTrg.Text) - k)
arr = Split(strTrg, Wt)
'On Error Resume Next 'vLookUP見つからない場合は飛ばす
For i = 0 To UBound(arr)
If Not buf <> "" Then
buf = Application.VLookup(arr(i), rVip, ix, n)
Else
buf = buf & "/" & Application.VLookup(arr(i), rVip, ix, n)
End If
Next
VlpRep = buf
End Function

数式例（画像の配置）
=VlpRep(C7:D10,A2,2,0,"//","/",1)
関数リストなどに登録するコードは割愛

この回答へのお礼

№１　様
こんにちわ。
ご回答いただきありがとうございます。
ユーザー定義関数については使用したことがないため、調べてみたいと思います。

お礼日時：2022/10/10 18:22