Compare Character Strings - VBA help

P

PIP7410

New Member
Joined
Jan 26, 2012
Messages
12
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
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.
 
Upvote 0
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"
Code: 
[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
 
Upvote 0
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
 
Upvote 0
Is you data like this:-
Code: 
[COLOR=RoyalBlue][B]Row No [/B][/COLOR] [COLOR=RoyalBlue][B]Col(A)                                    [/B][/COLOR]
1.      37ArnsideStreetRusholmeOL145PH            
2.      37ArnsideStreetRusholmeManchesterOL145PH
Regards Mick
 
Last edited:
Upvote 0
I'm not sure what you want with different inputs (e.g. "abcCATxyz" and "abcDOGxyz"), but perhaps this will work for you
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
WORDIF Formating in VBA
Replies
8
Views
2K
Akuini
Akuini
S
  • Question
Autofilter in VBA when criteria is has more words separated by a comma in 2 sheets
Replies
0
Views
473
shafiey
S
M
  • Question
VBA Code - Run Code if cell value is not blank
Replies
0
Views
431
MHamid
M
userxyz777
  • Solved
Remove the first character of a string if is a hyphen '-` or equal sign = with VBA
Replies
5
Views
2K
userxyz777
userxyz777
S
  • Solved
Moving files to a subfolder based on partial filename with VBA
Replies
9
Views
1K
Mr_Ragweed2
M

Forum statistics

Threads
1,214,832
Messages
6,121,843
Members
449,051
Latest member
excelquestion515

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top