Excel批量移除字符串内指定颜色代码子串的公式优化问题

针对你需要从字符串中移除所有匹配Table11[Color]列表的颜色代码子串（格式为-XXX）的需求，以下是几种高效方案：

方案1：Excel 365/2021 专属（推荐） #

使用REDUCE函数迭代处理所有颜色代码，结合排序避免短代码干扰长代码：

=REDUCE(Table1[@Name],SORT(Table11[Color],LEN(Table11[Color]),-1),LAMBDA(a,b,SUBSTITUTE(a,"-"&b,"")))

逻辑说明： #

1. SORT(Table11[Color],LEN(Table11[Color]),-1)：将颜色列表按字符串长度降序排序，优先替换长代码（比如先处理-BBDAC再处理-BB，避免短代码截断长代码）
2. REDUCE：以原字符串为初始值，遍历排序后的颜色列表，用SUBSTITUTE逐一移除所有-XXX格式的匹配子串

方案2：兼容多数Excel版本 #

使用TEXTJOIN + FILTERXML拆分并过滤字符串：

=TEXTJOIN("-",TRUE,FILTERXML("<t><s>"&SUBSTITUTE(Table1[@Name],"-","</s><s>")&"</s></t>","//s[not(.= '"&TEXTJOIN("','",TRUE,Table11[Color])&"')]"))

逻辑说明： #

1. SUBSTITUTE(...)：将原字符串的-替换为XML标签，把字符串拆分为独立节点
2. FILTERXML：通过XPath表达式筛选出不在颜色列表中的子串
3. TEXTJOIN：将筛选后的子串用-重新拼接，自动忽略空值

方案3：VBA自定义函数（适合旧版Excel） #

如果你的Excel版本不支持上述函数，可以创建自定义VBA函数：

Function RemoveColorCodes(inputStr As String, colorList As Range) As String
    Dim sortedColors As Variant
    Dim i As Integer
    Dim tempStr As String
    
    tempStr = inputStr
    '按长度降序排序颜色列表
    sortedColors = colorList.Value
    sortedColors = Application.Sort(sortedColors, 1, xlDescending, Header:=xlNo)
    
    '遍历移除所有匹配的颜色代码
    For i = LBound(sortedColors) To UBound(sortedColors)
        tempStr = Replace(tempStr, "-" & sortedColors(i, 1), "")
    Next i
    
    RemoveColorCodes = tempStr
End Function

使用方法： #

在单元格中输入：

=RemoveColorCodes(Table1[@Name],Table11[Color])

验证示例 #

以你提供的字符串11130FCO20LH-SP-MS为例：

• 颜色列表包含SP和MS
• 方案1/2/3处理后都会得到11130FCO20LH，完全符合你的期望结果

注意事项 #

• 如果颜色列表中存在互为子串的代码（比如M和MS），必须按长度降序处理，否则会出现错误替换
• 所有方案都会保留非颜色代码的分隔内容（比如12143-55-30-RH-ABZ中的RH不在颜色列表，会被保留，最终得到12143-55-30-RH）

内容的提问来源于stack exchange，提问作者Cody Schram