excel - 使用excel公式通过多个子字符串删除匹配的字符串

Question

如何通过该单词的熟悉 stub 列表删除或替换字符串中的熟悉单词？
列表包含约 40 个 stub (子字符串)。有了所有这些，我希望替换成数百个匹配的单词。
我对公式解决方案感兴趣，因为我已经知道如何做到这一点，编码 VBA。

我用 FILTERXML 玩 TEXTJOIN，但它不能用于大 stub 列表:
公式 1. FILTERXML(一一输入的 stub )

=IFERROR(TEXTJOIN(" ";;FILTERXML("<A><B>"&SUBSTITUTE(A2;" ";"</B><B>")&"</B></A>";"//B[not(contains(., 'auti') or contains(., 'AuTi') or contains(., 'tion') or contains(., 'mpl') or contains(., 'Mpl')or contains(., 'etc'))]"));"")

结果很好，但不幸的是区分大小写，因此为所有大写版本(Propercase、UPERCASE、小写、MixEdCase)一一输入的子字符串会生成一个非常大的公式，由于长度限制(255 个字符)而无法使用。
公式 2. FILTERXML(带案例翻译)

=IFERROR(TEXTJOIN(" ";;FILTERXML("<T><S>"&SUBSTITUTE(A2;" ";"</S><S>")&"</S></T>";"//S[not(contains(translate(., 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'abcdefghijklmnopqrstuvwxyz'),'auti')or contains(., 'tion')or contains(., 'mpl')or contains(., 'etc'))]"));"")

结果显然很好，但有一些奇怪的错过了大写匹配(检查附图中的单元格 D14)。为避免这种情况，我可以对每个 stub 使用翻译 translate(., 'AUTI', 'auti'),'auti')但长度限制不会让我使用所有列表。
公式 3. FILTERXML(降低所有字符串)

=IFERROR(TEXTJOIN(" ";;FILTERXML("<A><B>"&SUBSTITUTE(LOWER(A2);" ";"</B><B>")&"</B></A>";"//B[not(contains(., 'auti')or contains(., 'tion') or contains(., 'mpl') or contains(., 'etc'))]"));"")

结果很好，但输出字符串完全较低。而且长度限制仍然存在。

我知道 FILTERXML 是完成这项任务的好方法，但我不知道如何使用。不幸的是，Xpath 1.0 不允许 match() 之类的东西

Answer 1

非常好的问题，但是您正在将通过 Excel-2019 制作的公式的功能扩展到它的极限。考虑到 xpath 1.0 的限制(没有 matches() 也没有列表/数组处理:
B1 中的公式:

=TEXTJOIN(" ",,IF(ROW(A$1:INDEX(A:A,LEN(A2)))=TRANSPOSE(FILTERXML("<x><t>"&TEXTJOIN("</s></t><t>",,IFERROR(ROW(A$1:INDEX(A:A,COUNTA(FILTERXML("<t><s>"&SUBSTITUTE(A2," ","</s><s>")&"</s></t>","//s"))))&"<s>"&FILTERXML("<t><s>"&SUBSTITUTE(LOWER(A2)," ","</s><s>")&"</s></t>","//s"),""))&"</s></t></x>","//t[.//*[not(contains(., '"&TEXTJOIN("') or contains (., '",,{"auti","tion","mpl","etc"})&"'))]]")),FILTERXML("<t><s>"&SUBSTITUTE(A2," ","</s><s>")&"</s></t>","//s"),""))

我已经针对 40 个项目(例如 3-4 个字符长)对此进行了测试，结果确实如此 不是 超过 TEXTJOIN()的限制的参数也不是它可以处理的字符限制。
这是一个数组输入公式 (CSE)。
总结一下它的作用:

在空间上拆分您的输入(保持案例规范完整)；

使用 TEXTJOIN() 创建一个新的输入字符串使用另一个级别的 xpath childs 给每个单词一个索引；

与 FILTERXML()我们返回不包含您指定的任何数组元素的每个索引；

使用这些数字从第一步中检索所有节点并将它们连接在一起；

请注意，这会影响标点符号。

请注意，使用 ms365 的 lambda 辅助函数 REDUCE() 将变得更加容易。 :

=REDUCE(A1,{"auti","tion","mpl","etc"},LAMBDA(a,b,TEXTJOIN(" ",,FILTERXML("<t><s>"&SUBSTITUTE(a," ","</s><s>")&"</s></t>","//s[not(contains(translate(.,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz'), '"&b&"'))]"))))