Compare Character Strings - VBA help

[PIP7410]

Hi

I'm trying to compare 2 strings of characters and have the result be the difference only.
eg

37ArnsideStreetRusholmeOL145PH
37ArnsideStreetRusholmeManchesterOL145PH
Result: Manchester

I've tried using the Worddif VBA function but as my data has no spaces the result is the entire string. Is it possible to compare at character level?

I've included the worddif details below.

Thanks in advance!



Function WORDDIF(rngA As Range, rngB As Range) As String

Dim WordsA As Variant, WordsB As Variant
Dim ndxA As Long, ndxB As Long, strTemp As String

WordsA = Split(rngA.Text, " ")
WordsB = Split(rngB.Text, " ")

For ndxB = LBound(WordsB) To UBound(WordsB)
For ndxA = LBound(WordsA) To UBound(WordsA)
If StrComp(WordsA(ndxA), WordsB(ndxB), vbTextCompare) = 0 Then
WordsA(ndxA) = vbNullString
Exit For
End If
Next ndxA
Next ndxB

For ndxA = LBound(WordsA) To UBound(WordsA)
If WordsA(ndxA) <> vbNullString Then strTemp = strTemp & WordsA(ndxA) & " "
Next ndxA

WORDDIF = Trim(strTemp)

End Function

 

[JLGWhiz]

The function is designed to work with word which are separated by spaces. There are no spaces in your word group, so the function would not apply.

 

[PIP7410]

Thanks JLGWhiz - is there a function that can do what I'm trying to achieve?

 

[MickG]

This code is really a bit limited, but where your data is in a1 & a2 and "A2 is the larger string and similar to your thread example then it will return the difference , in this case "Manchester"

[COLOR="Navy"]Sub[/COLOR] MG27Nov16
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Str1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Str2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] S1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] S2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Str1 = [a1]
Str2 = [a2]
oMax = Application.Max(Len(Str1), Len(Str2))
[COLOR="Navy"]For[/COLOR] n = 1 To oMax
    [COLOR="Navy"]If[/COLOR] Not Mid(Str1, n, 1) = Mid(Str2, n, 1) [COLOR="Navy"]Then[/COLOR]
        S1 = S1 & Mid(Str1, n, 1)
        S2 = S2 & Mid(Str2, n, 1)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
MsgBox IIf(InStr(S2, S1) = 1, S2, Left(S2, InStr(S2, S1) - 1))
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[PIP7410]

Thanks Mick - when I run this I'm getting an error at:
MsgBox IIf(InStr(S2, S1) = 1, S2, Left(S2, InStr(S2, S1) - 1))
But I'm not sure what this line doing so I'm not sure what t doesn't like!

Thanks
Pippa

 

[MickG]

Is you data like this:-

[COLOR=RoyalBlue][B]Row No [/B][/COLOR] [COLOR=RoyalBlue][B]Col(A)                                    [/B][/COLOR]
1.      37ArnsideStreetRusholmeOL145PH            
2.      37ArnsideStreetRusholmeManchesterOL145PH

Regards Mick

 

[PIP7410]

Yes it is, although it wouldn't be hard to arrange it side by side in columns A & B

 

[mikerickson]

I'm not sure what you want with different inputs (e.g. "abcCATxyz" and "abcDOGxyz"), but perhaps this will work for you

Function strInnerDifference(aString As String, bString As String, Optional CaseSensitive As Boolean) As String
    Dim i As Long, j As Long
    If Not (CaseSensitive) Then aString = LCase(aString): bString = LCase(bString)
    
    For i = 1 To WorksheetFunction.Min(Len(aString), Len(bString))
        If Left(aString, 1) = Left(bString, 1) Then
            aString = Mid(aString, 2)
            bString = Mid(bString, 2)
        Else
            Exit For
        End If
    Next i
    
    For i = 1 To WorksheetFunction.Min(Len(aString), Len(bString))
        If Right(aString, 1) = Right(bString, 1) Then
            aString = Left(aString, Len(aString) - 1)
            bString = Left(bString, Len(bString) - 1)
        Else
            Exit For
        End If
    Next i
    
    If aString = vbNullString Then
        strInnerDifference = bString
    ElseIf bString = vbNullString Then
        strInnerDifference = aString
    Else
        strInnerDifference = aString & "," & bString
    End If
End Function

 

[MickG]

Here is the same example in a file !!!
https://app.box.com/s/c9ihqzpl5m0w9m7khaf0

 

[PIP7410]

Got it working, thanks very much for your help