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

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


On Nov 15, 1:38 pm, Albert wrote:
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

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.


--

Regards,
Nigel




"Albert" wrote in message
...
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

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

"JP" wrote:

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


On Nov 15, 1:38 pm, Albert wrote:
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

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


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


On Nov 15, 1:38 pm, Albert wrote:
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

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%...lk.com+soundex
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Nigel" wrote in message ...
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.


--

Regards,
Nigel




"Albert" wrote in message
...
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

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

On Nov 18, 10:45 pm, "David McRitchie"
wrote:
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%...lk.com+soundex
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

Thank you, no idea how that got left out.


HTH,
JP

On Nov 15, 4:41 pm, "IanKR" wrote:
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