String Compare... Tough one?

[Albert]

Hello!
I'm trying to make a function that compares two string expressions. HOWEVER,
I don't want the -1/0/1 returns that I get from the StrComp function. I would
like some sort of index or percentage that shows how similar the two strings
are. Has anyone done anything like this? Any ideas would be greatly
appreciated.
Best regards and thanx in advance,
Albert C

 

[JP]

Check out this function.


Function Str_Comp(st1 As String, st2 As String) As Double
'
' returns a number showing % comparison between two names
'
' i.e. =Str_Comp(A1, B1)
'
' Format cell as Percentage to make it look pretty!!
'
'
Dim MtchTbl(100, 100)
Dim MyMax As Double, ThisMax As Double
Dim i As Integer, j As Integer, ii As Integer, jj As Integer

With WorksheetFunction
st1$ = Trim$(.Proper(st1$))
st2$ = Trim$(.Proper(st2$))

MyMax# = 0

For i% = Len(st1$) To 1 Step -1
For j% = Len(st2$) To 1 Step -1
If Mid$(st1$, i%, 1) = Mid$(st2$, j%, 1) Then
ThisMax# = 0
For ii% = (i% + 1) To Len(st1$)
For jj% = (j% + 1) To Len(st2$)
If MtchTbl(ii%, jj%) > ThisMax# Then
ThisMax# = MtchTbl(ii%, jj%)
End If
Next jj%
Next ii%
MtchTbl(i%, j%) = ThisMax# + 1
If (ThisMax# + 1) > ThisMax# Then
MyMax# = ThisMax# + 1
End If
End If
Next j%
Next i%
Str_Comp = MyMax# / ((Len(st1$) + Len(st2$)) / 2)

End Function


HTH,
JP

 

[Nigel]

What constitutes a 'similar' string?

1. Same characters in string - unordered
2. Same character locations in string
3. Same order of characters
4. Same frequency of each character
5. Same number of characters
6. Same case of characters
7. Same value of strings

Some or all of the above or something else?

I guess the solution depends on what you weight as being important. Do you
have a specific requirement in mind, this might be better approached from
what are you trying to do with the result.

 

[Albert]

Thank you!
I will try it out.
Best regards,
Albert

 

[IanKR]

Check out this function.

Function Str_Comp(st1 As String, st2 As String) As Double
'
' returns a number showing % comparison between two names
'
' i.e. =Str_Comp(A1, B1)
'
' Format cell as Percentage to make it look pretty!!
'
'
Dim MtchTbl(100, 100)
Dim MyMax As Double, ThisMax As Double
Dim i As Integer, j As Integer, ii As Integer, jj As Integer

With WorksheetFunction
st1$ = Trim$(.Proper(st1$))
st2$ = Trim$(.Proper(st2$))

End With <================= need this here

 

[David McRitchie]

another one you could add to that is sounds like

Excel Developer Tip: Searching Using Soundex Codes
Soundex is an indexing system that translates a name into
a 4-digit code consisting of one letter and three numbers.
http://www.j-walk.com/ss/excel/tips/tip77.htm

http://www.google.com/search?q=site:j-walk.com+soundex

 

[JP]

FYI I've tried Soundex, it isn't nearly as accurate as the function I
posted, or others I've found.

Also here's a great one: http://hairyears.livejournal.com/115867.html


HTH,
JP

 

[JP]

Thank you, no idea how that got left out.


HTH,
JP