在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
- 我想要的结果应该在减法(A1-A2)之后的A3单元格中:
1,3,4,5,7,8
- If A1:
1,2,3,4,5,6,7,8,9
- And A2:
2,6,9
- My desired result should be in cell A3, after subtraction (A1-A2):
1,3,4,5,7,8
通过SUBSTITUTE
函数(按顺序)进行A2=2,3,4
时非常容易,但是我找不到上述解决方案.
It was very easy when A2=2,3,4
(serially) via SUBSTITUTE
function, however I can't find a solution to the above.
推荐答案
您已经标记了Formula和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: :这是一个数组公式,需要通过 Ctrl Shift Enter进行确认
注2: :这需要访问TEXTJOIN
功能,该功能可在Office 365和Excel 2019中使用.
Note2: This requires access to the TEXTJOIN
function, available in office 365 and Excel 2019.
Note3: 更有用的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屋!