我正在努力理解这个庞大的公式，想知道是否有人可以帮忙？
我有一个单元格 B7，其中包含任意值（户外、室内、3g 人造草皮等）。我希望它搜索该单元格，查看该单元格中的文本是否包含该值，如果包含，则替换，如果不包含，则继续检查下一个值。
很简单，对吧？
以下是我目前所掌握的信息：
=IF(SEARCH("outdoor",E7),SUBSTITUTE(B7,"<format>","outdoor"),"")=IF(SEARCH("indoor",E7),SUBSTITUTE(B7,"<format>","indoor"),"")=IF(SEARCH("9-hole",E7),SUBSTITUTE(B7,"<format>","9-hole"),"")=IF(SEARCH("18-hole",E7),SUBSTITUTE(B7,"<format>","18-hole"),"")=IF(SEARCH("club",E7),SUBSTITUTE(B7,"<format>","club"),"")=IF(SEARCH("venue",E7),SUBSTITUTE(B7,"<format>","venue"),"")=IF(SEARCH("astroturf",E7),SUBSTITUTE(B7,"<format>","astroturf"),"")=IF(SEARCH("3g-astroturf",E7),SUBSTITUTE(B7,"<surface>","3G astroturf"),"")=IF(SEARCH("grass",E7),SUBSTITUTE(B7,"<surface>","grass"),"")=IF(SEARCH("hard-macadam",E7),SUBSTITUTE(B7,"<surface>","hard macadam"),"")=IF(SEARCH("concrete",E7),SUBSTITUTE(B7,"<surface>","concrete"),"")=IF(SEARCH("clay",E7),SUBSTITUTE(B7,"<surface>","clay"),"")=IF(SEARCH("indoor",E7),SUBSTITUTE(B7,"<surface>","indoor"),"")=IF(SEARCH("artificial",E7),SUBSTITUTE(B7,"<surface>","artificial"),"")=IF(SEARCH("synthetic-rubber",E7),SUBSTITUTE(B7,"<surface>","synthetic rubber"),"")=SUBSTITUTE(B7,"<venue>",A7)
非常感谢您的帮助。谢谢
答案1
将您的单词列表放入其他单元格中：
然后在数组公式中引用这些单元格：
=TRIM(SUBSTITUTE(A1,INDEX($G$1:$G$3,MATCH(TRUE,ISNUMBER(SEARCH($G$1:$G$3,A1)),0)),""))
作为数组公式，退出编辑模式时需要使用 Ctrl-Shift-Enter 而不是 Enter 进行确认。如果操作正确，Excel 会将{}公式放在一边。
这只会替换在目标文本字符串中找到的列表中的第一个单词。如果列表中有两个或更多单词，则其他单词将保留。
