Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar Function in VBA - does it exist ?
Dear Experts
I have a need to compare 2 data items - Var1 and Var2 which are both strings Var1 may have "Aston Martin" within it and Var2 may have "Asto Martin" - i.e they are similar and a user has spelt Var2 incorrectly. I need to evaluate them so I can see in a long list they are the same - has VBA a function which compares Is Similar or has any one an idea how this may be done As always I appreciate your ideas and thoughts and thank you in advance Chris -- C Ward |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar Function in VBA - does it exist ?
How about the Like operator with wildcards, e.g.
Sub Test() Dim s1 As String, s2 As String s1 = "Aston Martin" s2 = "Asto Martin" s1 = "*" & Replace(s1, " ", "*") & "*" s2 = "*" & Replace(s2, " ", "*") & "*" Debug.Print s1 & " : " & s2 & " : " & (s1 Like s2) End Sub -- Regards, Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility http://www.higherdata.com/sql/batchsqlfromexcel.html Create batch SQL from Excel "christopher ward" wrote in message ... Dear Experts I have a need to compare 2 data items - Var1 and Var2 which are both strings Var1 may have "Aston Martin" within it and Var2 may have "Asto Martin" - i.e they are similar and a user has spelt Var2 incorrectly. I need to evaluate them so I can see in a long list they are the same - has VBA a function which compares Is Similar or has any one an idea how this may be done As always I appreciate your ideas and thoughts and thank you in advance Chris -- C Ward |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar Function in VBA - does it exist ?
Hi Tim
Not sure it would give me quite what I want as my example is simple in nature I do not know what or where the differences may be. I considered turning each letter into its ASCII code and then adding them up - If the two strings are 10% away they could be similar but even this does not solve the issue of they may have the same letters but not in the same order. Thanks for the try though - better than my idea I suspect -- C Ward "Tim Zych" wrote: How about the Like operator with wildcards, e.g. Sub Test() Dim s1 As String, s2 As String s1 = "Aston Martin" s2 = "Asto Martin" s1 = "*" & Replace(s1, " ", "*") & "*" s2 = "*" & Replace(s2, " ", "*") & "*" Debug.Print s1 & " : " & s2 & " : " & (s1 Like s2) End Sub -- Regards, Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility http://www.higherdata.com/sql/batchsqlfromexcel.html Create batch SQL from Excel "christopher ward" wrote in message ... Dear Experts I have a need to compare 2 data items - Var1 and Var2 which are both strings Var1 may have "Aston Martin" within it and Var2 may have "Asto Martin" - i.e they are similar and a user has spelt Var2 incorrectly. I need to evaluate them so I can see in a long list they are the same - has VBA a function which compares Is Similar or has any one an idea how this may be done As always I appreciate your ideas and thoughts and thank you in advance Chris -- C Ward |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar Function in VBA - does it exist ?
there's the LIKE method ... maybe you should replace spaces with asterisks
Sub test() Select Case similar("Aston Martin", "Asto Martin ") Case 0 MsgBox "No Match" Case 1 MsgBox "Exact Match" Case 2 MsgBox "Similar" Case Else End Select End Sub Function similar(text1 As String, text2 As String) As Long text1 = Replace(text1, " ", "*") text2 = Replace(text2, " ", "*") If text1 = text2 Then similar = 1 ElseIf text1 Like text2 Then similar = 2 End If End Function I noticed that this is exactly the same as Tim's response...hmmm HOWEVER what it does is to show you that a UDF could be used. You need to think of an algorithm, a rule if you will, that can be coded to define how the matching works. what is a "partial" match? The human brain recognizes patterns awesomely fast, but to do this with a computer means a huge amount of coding - and probably outside the scope of your project. "christopher ward" wrote in message ... Dear Experts I have a need to compare 2 data items - Var1 and Var2 which are both strings Var1 may have "Aston Martin" within it and Var2 may have "Asto Martin" - i.e they are similar and a user has spelt Var2 incorrectly. I need to evaluate them so I can see in a long list they are the same - has VBA a function which compares Is Similar or has any one an idea how this may be done As always I appreciate your ideas and thoughts and thank you in advance Chris -- C Ward |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar Function in VBA - does it exist ?
christopher ward wrote:
Dear Experts I have a need to compare 2 data items - Var1 and Var2 which are both strings Var1 may have "Aston Martin" within it and Var2 may have "Asto Martin" - i.e they are similar and a user has spelt Var2 incorrectly. I need to evaluate them so I can see in a long list they are the same - has VBA a function which compares Is Similar or has any one an idea how this may be done A crude method that *will* give false positives is to sort the string into alphabetical order. Then apply the Like match suggested earlier. It is more often used for anagrams and crossword solvers. This will get transposed letters and single typos extra or missing. You could probably ignore non-alphanumerics too. Regards, Martin Brown |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar Function in VBA - does it exist ?
You don't say what your ultimate objective is, but I assume it is to identify
misspelt names and correct them. If so, why not use the spell checker with a custom dictionary? Your dictionary would contain Aston and Martin but not Asto. HTH "christopher ward" wrote: Dear Experts I have a need to compare 2 data items - Var1 and Var2 which are both strings Var1 may have "Aston Martin" within it and Var2 may have "Asto Martin" - i.e they are similar and a user has spelt Var2 incorrectly. I need to evaluate them so I can see in a long list they are the same - has VBA a function which compares Is Similar or has any one an idea how this may be done As always I appreciate your ideas and thoughts and thank you in advance Chris -- C Ward |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar Function in VBA - does it exist ?
- If the two strings are 10% away they could be similar
...they may have the same letters but not in the same order. s1 = "Aston Martin"; s2 = "Asto Martin" ; ? EditDistance(s1, s2) 1 (ie they are close, but not equal) Perhaps one of a few variations along this line from computer science... http://en.wikipedia.org/wiki/Edit_distance = = = = = = = Hth Dana DeLouis christopher ward wrote: Hi Tim Not sure it would give me quite what I want as my example is simple in nature I do not know what or where the differences may be. I considered turning each letter into its ASCII code and then adding them up - If the two strings are 10% away they could be similar but even this does not solve the issue of they may have the same letters but not in the same order. Thanks for the try though - better than my idea I suspect |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
looking for a function that hopefully exist | Excel Worksheet Functions | |||
Copy Worksheet to a new Workbook creating if it doesn't exist and add more Worksheets if it does exist | Excel Programming | |||
Exist a function to... | Excel Worksheet Functions | |||
Command Line. How to tell to XL : If the xls file exist : Open it, if it does not exist : Create it. | Excel Programming | |||
Does such a function exist ? | Excel Worksheet Functions |