How can I subtract a comma separated string from another one in Excel? I want to subtract, A1 and A2 from A3 .
A1 - Wed,Thu
A2- Sun, Mon
A3- Mon,Tue,Wed,Thu,Fri,Sat,Sun
A4- Tue,Fri,Sat
I tried the following :-
=IF(ISERROR(SEARCH(A1,A3,1)),A3,RIGHT(A3,LEN(A3)-LEN(A1)))
But the output is not as what I wanted. PS :- The A4 answer is the desired answer and not the answer I got after writing the above formula.
I'm assuming these values are delimited by comma-space. If not then adjust the formula accordingly (or use a double substitute to get rid of spaces before changing commas to tags).
In A4 try:
=TEXTJOIN(", ",1,FILTERXML(SUBSTITUTE("<t><s>"&A3&", "&A2&", "&A1&"</s></t>",", ","</s><s>"),"//s[not(following::*=. or preceding::*=.)]"))
Enter through CtrlShiftEnter
If FILTERXML does spark your interest, then have a look here for more possibilities.
Consider the following User Defined Function:
Option Explicit
Public Function RemoveItems(BigString As String, LittleString As String) As String
Dim Good As Boolean, L, B, arrB, arrL
Application.Volatile
arrB = Split(Replace(BigString, " ", ""), ",")
If InStr(LittleString, ",") = 0 Then
For Each B In arrB
If LittleString = B Then
Else
RemoveItems = RemoveItems & "," & B
End If
Next B
Else
arrL = Split(Replace(LittleString, " ", ""), ",")
For Each B In arrB
Good = True
For Each L In arrL
If L = B Then Good = False
Next L
If Good Then RemoveItems = RemoveItems & "," & B
Next B
End If
If RemoveItems <> "" Then RemoveItems = Mid(RemoveItems, 2)
End Function
It will remove items from the first comma-separated-string that appear in the second comma-separated-string. for example:
In your example, you would nest the calls:
=RemoveItems(RemoveItems(A3,A1),A2)
NOTES:
