0

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

black138
  • 3
  • 2
  • 2
    Replace all `,` to `-` AND then recplace all `~` to `-` and in the end use `-` as delimiter to split. Otherwise use the mighty Regular Expressions like this: https://regex101.com/r/uzrmG0/1 • For more how to use it see: [How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops). Give it a try yourself and if you got stuck or errors come back with your code ([No attempt was made](http://idownvotedbecau.se/noattempt/)). – Pᴇʜ Oct 15 '20 at 13:08
  • Do you have unknown number of delimiters, or can the delimiter be `,`, `~`, or `-` only? – Light Oct 16 '20 at 01:13
  • Yes, it will be unknown number of delimiters, not just these only... – black138 Oct 16 '20 at 01:17
  • If anything besides letters and numbers can be a delimiter, then I recommend Regular Expressions! – Pᴇʜ Oct 16 '20 at 06:14
  • @black138 Note you read from `Sheet1` but you write to a different sheet `Worksheets("Testing")` Your code defenitely replaces every delimiter to comma, I tested it. – Pᴇʜ Oct 19 '20 at 07:28
  • @Pᴇʜ Yep, it works ady, thank you! May I have simple code to know how to use REGEX to compare the element, and the number of element will not be constant? I am not familiar in using REGEX – black138 Oct 20 '20 at 00:43
  • @black138 example code for RegEx and how to use it is in my first comment already. Give it a try yourself to adapt it to your problem. If you get stuck or errors open up a new question here at Stack Overflow post the code you have tried and tell what went wrong or where you got stuck. A full [mcve] and the example data to reproduce your issue will help people to give a good answer. – Pᴇʜ Oct 20 '20 at 06:20

1 Answers1

0

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.

Danny Schneider
  • 311
  • 2
  • 9