Similar Function in VBA - does it exist ?

[christopher ward]

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

 

[Tim Zych]

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]

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

 

[Patrick Molloy]

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.

 

[Martin Brown]

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

 

[aardvark]

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

 

[Dana DeLouis]

- 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