Excel用 "lookup"(工作表B)中的相应字符串替换单元格(工作表A)中的子字符串

Question

如何在 MS Excel 15.4 中执行此操作

我要处理 A栏成为 B栏

Column A                  | Column B
----------------------------------------------------------------------
one, 1, two, 2, three, 3  | one apple, two bananas, three strawberries
one, 1, four, 4           | one apple, four oranges
.......................
... many other rows ...
.......................
two, 2, four, 4, three, 3 | two bananas, four oranges, three strawberries    

A栏在查找表中可以有 n 个匹配的子字符串。

我有另一张表(查找表)用什么替换 中的文本A栏和

Match col | Replace col
----------------------------
one, 1    | one apple
two, 2    | two bananas
three, 3  | three strawberries
four, 4   | four oranges
... and many more ...

我想替换 中的所有子字符串A栏与 替换列 查找表的值

看来我可以合并 VLOOKUP与 SUBSTITUTE ，但我正在为此苦苦挣扎

Answer 1

在单个细胞中进行；

=IFERROR(VLOOKUP(TRIM(SUBSTITUTE(MID(SUBSTITUTE($A1,",",REPT(" ",999)),(COLUMN(A:A)-1)*2*999+1,2*999),REPT(" ",999),",")),Sheet2!$A:$B,2,FALSE),"")

如果您订阅了 Office 3651 excel，则可以使用此数组公式将其全部放在一个单元格中:

=TEXTJOIN(",",TRUE,IFERROR(LOOKUP(TRIM(SUBSTITUTE(MID(SUBSTITUTE($A1,",",REPT(" ",999)),(ROW(INDIRECT("1:" & INT((LEN($A1)-LEN(SUBSTITUTE($A1,",","")))/2)+1))-1)*2*999+1,2*999),REPT(" ",999),",")),Sheet2!A:A,Sheet2!B:B),""))

作为一个数组公式，退出编辑模式时必须使用 Ctrl-Shift-Enter 而不是 Enter 来确认。如果正确完成，Excel 将输入 {}围绕公式。

需要注意的是，引用数据必须在查找列上进行排序:





1 如果您没有 Office 365 但想使用此公式，您可以将我的答案中的代码 HERE这将模仿附加到工作表的模块中的 TEXTJOIN() 。然后使用上述公式