Compare two strings, and find difference?

[EtherBoo]

This may not be possible with excel, but this is what I'm trying to do

A1 = Hello John, my name is Steve, how was your day?
B1 = Hello John, my name is Steve, was day?

I need C1 to return:
how your

Is this possible?

 

[Me_In_Va]

I tried this VBA in my spreadsheet but it is returning the characters that are the same in both columns. Even changing the one line as stated in another post in this thread I only get what is the same in both cells, not what is different.
I am running Excel 2010 on Win7.

My example I used was:
A1 = ABC
B1 = ABCDEF
C1 = ABC

C1 has the formula "=WORDDIF(A1,B1)"

I want the result in C1 to be "DEF", to show the difference between A1 & B1.

Thanks!

Here's a custom function called WORDDIF that may do what you want.

To install the custom function...

• Alt+F11 to open the VBA Editor
• From the VBA menu, select Insert\ Module
• Paste the code below in the VBA Edit window

Back in Excel, put this formula in C1
=WORDDIF(A1,B1)

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

 

[AlphaFrog]

The WORDDIF function compares words and not individual characters. It returned ABC in your example because it evaluates ABC as a word that doesn't exist in the sentence ABCDEF.

This CHARDIF function does a case insensitive 1-to-1 character comparison.

[COLOR=darkblue]Function[/COLOR] CHARDIF(rngA [COLOR=darkblue]As[/COLOR] Range, rngB [COLOR=darkblue]As[/COLOR] Range) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    [COLOR=darkblue]Dim[/COLOR] strA [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], strB [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], strTemp As [COLOR=darkblue]String[/COLOR]
    
    strA = rngA.Value
    strB = rngB.Value
    [COLOR=darkblue]If[/COLOR] Len(strA) > Len(strB) [COLOR=darkblue]Then[/COLOR]
        strTemp = strA
        strA = strB
        strB = strTemp
        strTemp = ""
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        
    [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] Len(strB)
        [COLOR=darkblue]If[/COLOR] i > Len(strA) [COLOR=darkblue]Then[/COLOR]
            strTemp = strTemp & Mid(strB, i, 1)
        [COLOR=darkblue]ElseIf[/COLOR] UCase(Mid(strA, i, 1)) <> UCase(Mid(strB, i, 1)) [COLOR=darkblue]Then[/COLOR]
            strTemp = strTemp & Mid(strB, i, 1)
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] i
    
    CHARDIF = strTemp
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]

 

[Me_In_Va]

Thank you! I learned something new. Anything I learn about VBA will be something new.

Thank you again! I will test this when I get back to my desk and let you know how it works for me.

The WORDDIF function compares words and not individual characters. It returned ABC in your example because it evaluates ABC as a word that doesn't exist in the sentence ABCDEF.

This CHARDIF function does a case insensitive 1-to-1 character comparison.

[COLOR=darkblue]Function[/COLOR] CHARDIF(rngA [COLOR=darkblue]As[/COLOR] Range, rngB [COLOR=darkblue]As[/COLOR] Range) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    [COLOR=darkblue]Dim[/COLOR] strA [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], strB [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], strTemp As [COLOR=darkblue]String[/COLOR]
    
    strA = rngA.Value
    strB = rngB.Value
    [COLOR=darkblue]If[/COLOR] Len(strA) > Len(strB) [COLOR=darkblue]Then[/COLOR]
        strTemp = strA
        strA = strB
        strB = strTemp
        strTemp = ""
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        
    [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] Len(strB)
        [COLOR=darkblue]If[/COLOR] i > Len(strA) [COLOR=darkblue]Then[/COLOR]
            strTemp = strTemp & Mid(strB, i, 1)
        [COLOR=darkblue]ElseIf[/COLOR] UCase(Mid(strA, i, 1)) <> UCase(Mid(strB, i, 1)) [COLOR=darkblue]Then[/COLOR]
            strTemp = strTemp & Mid(strB, i, 1)
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] i
    
    CHARDIF = strTemp
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]

 

[newbie0717]

Hello Alphafrog,
The CHARDIF function is great. Would it be possible to be able to highlight/color the differences?
For example,

A1: ABCDEFG
B1: CDCDECD
C1: CDCDECD
or another output could be
C1: CD---CD

I would greatly appreciate any suggestions.

 

[AlphaFrog]

A function like this cannot change formatting.

It could be done with a macro. This prompts the user to select two cells or two ranges that are the same size.

[COLOR=darkblue]Sub[/COLOR] CHARDIFS()
    
    [COLOR=darkblue]Dim[/COLOR] rngA [COLOR=darkblue]As[/COLOR] Range, rngB [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] strA [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], strB [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], r [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], c [COLOR=darkblue]As[/COLOR] Long
    
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]Set[/COLOR] rngA = Application.InputBox("Select the 1st cell(s) to compare to.", "Select Range A", Type:=8)
    [COLOR=darkblue]If[/COLOR] rngA [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]Do[/COLOR]
        [COLOR=darkblue]Set[/COLOR] rngB = Application.InputBox("Select the 2nd cell(s) to compare.", "Select Range B", Type:=8)
        [COLOR=darkblue]If[/COLOR] rngB [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
        [COLOR=darkblue]If[/COLOR] rngA.Columns.Count = rngB.Columns.Count And rngA.Rows.Count = rngB.Rows.Count [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Do[/COLOR]
        MsgBox "Range B muct have the same number of rows and columns as Range A.", vbExclamation, "Different Size Ranges"
        [COLOR=darkblue]Set[/COLOR] rngB = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Loop[/COLOR]
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    
    [COLOR=darkblue]For[/COLOR] c = 1 [COLOR=darkblue]To[/COLOR] rngA.Columns.Count
        [COLOR=darkblue]For[/COLOR] r = 1 [COLOR=darkblue]To[/COLOR] rngA.Rows.Count
            strA = rngA(r, c).Value
            strB = rngB(r, c).Value
            [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] Len(strB)
                [COLOR=darkblue]If[/COLOR] i > Len(strA) [COLOR=darkblue]Or[/COLOR] UCase(Mid(strA, i, 1)) <> UCase(Mid(strB, i, 1)) [COLOR=darkblue]Then[/COLOR]
                    rngB(r, c).Characters(Start:=i, Length:=1).Font.ColorIndex = 3
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] i, r, c
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[newbie0717]

Thank you very much AlphaFrog. It worked perfectly, you are awesome!

One question I had was that I noticed.... if I used the macro comparing columns A and B with formulas already contained in the cell (I used formula to trim and extract text string), then the macro highlights everything in red. However, if I compare A and B without formulas in the cells, then the macro highlights correctly. So what I do then is to copy and paste the two columns that I want to compare without formulas into new columns and then run the macro which highlights perfectly. Is there another way to do this instead of copy and paste?
If not that is okay, the macro works great.
I really appreciate your time and help.
Thank you.

 

[AlphaFrog]

The macro could be made to look at the two columns with formulas and output the colored result to to a third column.

This will output the results one column to the left (+1) of Range B.

Sub CHARDIFS()
    
    Dim rngA As Range, rngB As Range
    Dim strA As String, strB As String
    Dim i As Long, r As Long, c As Long
    
    On Error Resume Next
    Set rngA = Application.InputBox("Select the 1st cell(s) to compare to.", "Select Range A", Type:=8)
    If rngA Is Nothing Then Exit Sub
    Do
        Set rngB = Application.InputBox("Select the 2nd cell(s) to compare.", "Select Range B", Type:=8)
        If rngB Is Nothing Then Exit Sub
        If rngA.Columns.Count = rngB.Columns.Count And rngA.Rows.Count = rngB.Rows.Count Then Exit Do
        MsgBox "Range B muct have the same number of rows and columns as Range A.", vbExclamation, "Different Size Ranges"
        Set rngB = Nothing
    Loop
    On Error Resume Next
    
    For c = 1 To rngA.Columns.Count
        For r = 1 To rngA.Rows.Count
            strA = rngA(r, c).Value
            strB = rngB(r, c).Value
            rngB(r, c [COLOR=#ff0000]+ 1[/COLOR]).Value = strB
            rngB(r, c [COLOR=#ff0000]+ 1[/COLOR]).Font.ColorIndex = xlAutomatic
            For i = 1 To Len(strB)
                If i > Len(strA) Or UCase(Mid(strA, i, 1)) <> UCase(Mid(strB, i, 1)) Then
                    rngB(r, c[COLOR=#ff0000] + 1[/COLOR]).Characters(Start:=i, Length:=1).Font.ColorIndex = 3
                End If
    Next i, r, c
    
End Sub

 

[newbie0717]

Awesome! Thank you , thank you very much.
I have been looking around for months for something like this.
Thanks again.

 

[AlphaFrog]

You're welcome. Thanks for the feedback.

In thinking about it, if you delete the three red +1 's in the code, it will just overwrite the Range B formulas with the colored results.

 

[newbie0717]

Thank you Alphafrog. It is even better.