I need to compare the data of two single cells to check the difference in both data. The data's format are just like "C1,C2,C3",but the separator may be change due to the given record. It may change to something like "C1~C2~C3" or even "C1-C2-C3". I tried to use split function but the delimiter is inflexible, whereas I have to check for the delimiter first in order to split the data into an array, and I tried to loop for the data to find the delimiter but it doesn't work. May I know how can I overcome this problem, or any suggestion which are more concise? Sorry for poor English and expression ability...
I tried to replace too
Sub ChangeDelim()
Dim i As Variant
Dim delimList() As Variant
Dim replacement As String
Dim str1 As String
Dim str2 As String
delimList = Array("@", ".", "~", "-", "_") 'may increase due to requirement
replacement = ","
str1 = Sheet1.Range("A4")
str2 = Sheet1.Range("B4")
For Each i In delimList
str1 = Replace(str1, i, replacement)
str2 = Replace(str2, i, replacement)
Next i
With Worksheets("Testing")
.Range("D4") = str1
.Range("E4") = str2
End With
End Sub
But it looks like doesn't work since the output still the same
As it seams that you are using VBA you can you RegEx to compare the strings OR to remove the delimiters OR replace them by any default delimiter for the comparison.
