在Excel中从另一个逗号分隔的字符串中减去逗号分隔的字符串 [英] Subtracting a comma separated string from another one in Excel

问题描述

如何从 Excel 中的另一个字符串中减去逗号分隔的字符串?

How can I subtract a comma seperated string from another one in Excel?

• 如果 A1:1,2,3,4,5,6,7,8,9
• 和A2:2,6,9
• 我想要的结果应该在单元格 A3 中，减法后 (A1-A2):1,3,4,5,7,8

A2=2,3,4(串行)通过 SUBSTITUTE 函数时很容易，但是我找不到上述问题的解决方案.

It was very easy when A2=2,3,4 (serially) via SUBSTITUTE function, however I can't find a solution to the above.

推荐答案

您已经标记了公式和 VBA.所以我也给你两个选择:

You have both tagged formula and VBA. So let me give you two options too:

1) 公式

=TEXTJOIN(",",1,FILTERXML("<t><s>"&SUBSTITUTE(A1&","&A2,",","</s><s>")&"</s></t>","//s[not(following::*=. or preceding::*=.)]"))

注1:这是一个数组公式，需要通过CtrlShift回车确认

注意 2:这需要访问 TEXTJOIN 功能，在 Office 365 和 Excel 2019 中可用.

Note2: This requires access to the TEXTJOIN function, available in office 365 and Excel 2019.

注意 3: 可以找到更有用的 FILTERXML技巧"这里

2) UDF

我建议使用 Split 和 Join 函数来做到这一点:

I'd recommend using Split and Join functions to do this:

Function TxtFilter(val1 As String, val2 As String, sep As String) As String

Dim arr1 As Variant, arr2 As Variant
Dim x As Long
Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")

arr1 = Split(val1, sep)
arr2 = Split(val2, sep)

'Iterate first array to load dictionary
For x = LBound(arr1) To UBound(arr1)
    dict(arr1(x)) = 1
Next x

'Iterate second array to remove from dictionary
For x = LBound(arr2) To UBound(arr2)
    If dict.Exists(arr2(x)) Then dict.Remove arr2(x)
Next x

'Join remainder back together
TxtFilter = Join(dict.keys, sep)

End Function

通过=TxtFilter(A1,A2,",")

这篇关于在Excel中从另一个逗号分隔的字符串中减去逗号分隔的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！