英文:
Excel Replace Text via Another Ranges
问题
在A列中，我有一段长文本，其中包含可以在另一个范围（D列）中找到的子字符串。
我想要搜索该子字符串，如果找到了，就用E列中的值替换它。
任何帮助都将不胜感激。
英文:
In column A i have a long text, containing substrings that you can find on another range (column D).
I want to search for the substring and if I find it then replace it with the value in column E.
Any help will be much appreciated.
答案1
得分: 1
如果您使用Excel 365，可以在B列中使用以下公式：
=LET(mapping, tblMapping,
REDUCE([@text],INDEX(mapping,,1)
LAMBDA(a,b,SUBSTITUTE(a,b,XLOOKUP(b,INDEX(mapping,,1),INDEX(mapping,,2))))))
如果有两个或更多相关的文本也可以使用这个公式 - 参见最后一行
不要忘记在B列上应用 "自动换行"。
根据评论中的问题进行编辑：
您可以使用以下公式返回true/false，取决于是否至少找到一个文本：
=REDUCE(FALSE,tblMapping[Text to search],
LAMBDA(a,b,IF(a=TRUE,a,IFERROR(FIND(b,[@text])>0,FALSE))))
英文:
If you have Excel 365 you can use this formula in column B:
=LET(mapping, tblMapping,
REDUCE([@text],INDEX(mapping,,1)
LAMBDA(a,b,SUBSTITUTE(a,b,XLOOKUP(b,INDEX(mapping,,1),INDEX(mapping,,2))))))
This will also work if there are two or more relevant texts - s. last row
Don't forget to apply "wrap text" to column B.
EDIT according to question in comments:
You can use this formula to return true / false depending on wether at least one text has been found:
=REDUCE(FALSE,tblMapping[Text to search],
LAMBDA(a,b,IF(a=TRUE,a,IFERROR(FIND(b,[@text])>0,FALSE))))
答案2
得分: 0
以下是翻译好的内容：
• Formula used in cell B3
=LET(
a,[@Text],
b,FILTER(Table2,ISNUMBER(FIND(Table2[Text to search],a))),
c,INDEX(b,,1),
d,INDEX(b,,2),
IFERROR(SUBSTITUTE(a,c,d),a))
Since in one of the cells you are using line-feed, hence please remember to use wrap text, by hitting ALT+H+W for the desired result column.
英文:
You could try using the following formula, which is based on your data presented on the screenshot above.
• Formula used in cell B3
=LET(
a,[@Text],
b,FILTER(Table2,ISNUMBER(FIND(Table2[Text to search],a))),
c,INDEX(b,,1),
d,INDEX(b,,2),
IFERROR(SUBSTITUTE(a,c,d),a))
Since in one of the cells you are using line-feed, hence please remember to use wrap text, by hitting <kbd>ALT</kbd>+<kbd>H</kbd>+<kbd>W</kbd> for the desired result column.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论