0

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.

JvdV
  • 70,606
  • 8
  • 39
  • 70
Milan Kumar
  • 25
  • 7
  • 1
    Hi Milan, please consider editing your question to make it nicer. Do not use all caps, and elaborate more on your problem. What have you tried so far? – Pablo Mar 07 '20 at 08:09
  • It would be easy with VBA / UDF .. Use this for a starting point... https://stackoverflow.com/questions/60565666/vba-countif-using-arrays-as-arguments/60566417#60566417 – Naresh Mar 07 '20 at 08:44
  • Please don't change the question if you have another question. Post another quesiton instead. Refer to this question if needed. – JvdV Apr 15 '20 at 07:38
  • For the sake of future Googlers: https://www.mrexcel.com/board/threads/extracting-differences-between-two-comma-separated-strings.1089224/post-5234846 provides a concise solution if what you need is **set difference**, even though the function is confusingly named `GetUnique`. – Nickolay Nov 25 '22 at 00:34

1 Answers1

11

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,",")

JvdV
  • 70,606
  • 8
  • 39
  • 70