string comparasion

[Guest]

Hello;

I have 2 strings I need to compare..

example...
string #1
box of dates.

string #2
boxes of dates.

notice the extra es in string 2..

I need a way to identify what characters are extra
as in...

string 2 position 4 e position 6 s


I know this can be done, but for the life of me I can not get what my boss
wants...

Can anyone help...

thank in advance..

I am writing this as a basic excel macro
(and I will accept it as a formula also if that is better)

Excel 2003 (11.5612)

 

[Guest]

See if this helps. There's no problem determining when the contents of the
two diverge - it's figuring out where they come 'back together' that gets a
little tough. The reason I show a move of strTest1 and strTest2 into other
variables is that the loops need to be controlled by the length of the
shorter string.

This will tell you that characters 4-6 of the longer string don't match up
for your 2 test cases, but if the longer string was "boxes of dates, fresh"
then it will tell you that everything from 4-15 doesn't match even though
some of it does, it's just offset in there.

Sub CompareStrings()
Dim strTest1 As String
Dim strTest2 As String
Dim ShortString As String
Dim LongString As String
Dim LC As Integer
Dim Counter As Integer
Dim FirstBreakAt As Integer
Dim SecondBreakAt As Integer

strTest1 = "box of dates."
strTest2 = "boxes of dates."
'move them into proper variables
If Len(strTest1) <= Len(strTest2) Then
ShortString = strTest1
LongString = strTest2
Else
ShortString = strTest2
LongString = strTest1
End If
For LC = 1 To Len(ShortString)
If Mid(ShortString, LC, 1) <> Mid(LongString, LC, 1) Then
FirstBreakAt = LC
Exit For
End If
Next
Counter = 0
For LC = Len(ShortString) To 1 Step -1
If Mid(ShortString, Len(ShortString) - Counter, 1) _
<> Mid(LongString, Len(LongString) - Counter, 1) Then
SecondBreakAt = LC - 1
Exit For
End If
Counter = Counter + 1
Next
If FirstBreakAt = 0 And SecondBreakAt = 0 Then
MsgBox "Strings are duplicates"
Else
MsgBox "Characters " & FirstBreakAt _
& " through " & _
FirstBreakAt + SecondBreakAt _
& " of '" & LongString _
& "' don't match."
End If
End Sub