Q:

Subtracting a comma separated string from another one in Excel

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

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

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

A:

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

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

Note1: This is an array formula and needs to be confirmed through CtrlShiftEnter
Note2: This requires access to the TEXTJOIN function, available in office 365 and Excel 2019.
Note3: More usefull FILTERXML "tricks" can be found here

2) UDF
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

Call in any cell through =TxtFilter(A1,A2,",")

