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?

 

[jedin]

Hi AlphaFrog,

I've tried out your Sub Chardifs() macro and it works like a charm. I was wondering if it's possible to change the command such that users would not have to manually select the two ranges. Instead if it could be applied automatically to specific ranges.

For instance, I have values in Column A and in Column B, and then in Column D and in Column E that I would like to compare. It would be great if the macro could compare these two sets of columns and put the results in Column C and in Column F, respectively. Is this possible?

Thank you so much. Here is the original Sub Chardifs() command I am using:

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 + 1).Value = strB
rngB(r, c + 1).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 + 1).Characters(start:=i, Length:=1).Font.ColorIndex = 3
End If
Next i, r, c

End Sub

 

[AlphaFrog]

[color=darkblue]Sub[/color] CHARDIFSabde()
    
    [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] Long, col [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] col [color=darkblue]In[/color] Array("A", "D")
        [color=darkblue]Set[/color] rngA = Range(col & "1", Range(col & Rows.Count).End(xlUp))
        [color=darkblue]Set[/color] rngB = rngA.Offset(, 1)
        [color=darkblue]For[/color] r = 1 [color=darkblue]To[/color] rngA.Rows.Count
            strA = rngA(r, 1).Value
            strB = rngB(r, 1).Value
            rngB(r, 2).Value = strB
            rngB(r, 2).Font.ColorIndex = xlAutomatic
            [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, 2).Characters(Start:=i, Length:=1).Font.ColorIndex = 3
                [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]Next[/color] i, r, col
        
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[jedin]

Thank you so much! I really appreciate the fast response. I eventually figured it out, though using a much less elegant approach. That is to replace the

Set rngA = Application.InputBox("Select the 1st cell(s) to compare to.", "Select Range A", Type:=8)


with this line:

Set rngA = Range("B3:B400")

I am running into an issue though. I’m noticing that the command doesn’t work for cells that are made entirely of numbers. For example, I am comparing values in Column A with values in Column B where

A = 12345
B = 13345

I expected Column C to show 13345 where 3 is in red. I am getting instead 13345 where everything is in black.

On some troubleshooting, I realized that if I added characters in the cell that the command would work. For example if

A=12345-1
B=13345-1

Then the value in Column C is as expected where the 3 is in red (13345). I don’t want to add characters though and wanted to know if there’s a way around this.

Thank you!



Below is the original code:

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
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).Characters(Start:=i, Length:=1).Font.ColorIndex = 3
End If
Next i, r, c

End Sub

 

[AlphaFrog]

You're welcome.

Please use CODE tags when posting your code. It makes reading it much easier. See my signature block below.

Sub CHARDIFSabde()
    
    Dim rngA As Range, rngB As Range
    Dim strA As String, strB As String
    Dim i As Long, r As Long, col As Variant
    
    On Error Resume Next
    
    For Each col In Array("A", "D")
        Set rngA = Range(col & "3", Range(col & Rows.Count).End(xlUp))
        Set rngB = rngA.Offset(, 1)
        For r = 1 To rngA.Rows.Count
            strA = rngA(r, 1).Value
            strB = rngB(r, 1).Value
[COLOR=#ff0000]            rngB(r, 2).Value = IIf(IsNumeric(strB), "'", "") & strB[/COLOR]
            rngB(r, 2).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, 2).Characters(Start:=i, Length:=1).Font.ColorIndex = 3
                End If
            Next i, r, col
        
End Sub

 

[jedin]

You sir, are a genius. Thank you so very much. You just saved my life. It works perfectly =)

You're welcome.

Please use CODE tags when posting your code. It makes reading it much easier. See my signature block below.

Sub CHARDIFSabde()
    
    Dim rngA As Range, rngB As Range
    Dim strA As String, strB As String
    Dim i As Long, r As Long, col As Variant
    
    On Error Resume Next
    
    For Each col In Array("A", "D")
        Set rngA = Range(col & "3", Range(col & Rows.Count).End(xlUp))
        Set rngB = rngA.Offset(, 1)
        For r = 1 To rngA.Rows.Count
            strA = rngA(r, 1).Value
            strB = rngB(r, 1).Value
[COLOR=#ff0000]            rngB(r, 2).Value = IIf(IsNumeric(strB), "'", "") & strB[/COLOR]
            rngB(r, 2).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, 2).Characters(Start:=i, Length:=1).Font.ColorIndex = 3
                End If
            Next i, r, col
        
End Sub

 

[pypdf2]

Hi AlphaFrog,

Sorry to resurrect this thread once again. The following code works well for what I need with some exceptions:

Code:

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

I'm comparing two numbers and trying to extract the digits after the first difference. Here's an example that works with the above code:

535243 and 535438 returns 438, which is what I want.

Here's what doesn't work:

535656 and 535957 returns 97, when really I want 957. Is there a solution to this?

Many thanks.

 

[AlphaFrog]

This lists all digits remaining after it finds the first digit that was different.

[color=darkblue]Function[/color] DIGIDIF(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 [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] bFirstDiff As [color=darkblue]Boolean[/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] bFirstDiff [color=darkblue]Or[/color] UCase(Mid(strA, i, 1)) <> UCase(Mid(strB, i, 1)) [color=darkblue]Then[/color]
            strTemp = strTemp & Mid(strB, i, 1)
            bFirstDiff = [color=darkblue]True[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] i
    
    DIGIDIF = strTemp
    
[color=darkblue]End[/color] [color=darkblue]Function[/color]

 

[pypdf2]

Thank you!

 

[ElaineTay]

Hi AlphaFrog,
Sorry to bring out this post again! I have tried the WORDIF and CHARIF functions and it works great, but not sure if I can use vba to do the following in a Sub:

String1: Hi John, this is Elaine, how was your day?
String2: Hi John. This is Elaine, was day?

The program should be able to highlight the "," after John in String1, highlight the "t" in the word "this" in String1, and show the strikeout effect on the word "how" and "your" in String1, to show the differences String2 has against String1.
Not sure if this is possible, please enlighten me on this thanks!

 

[AlphaFrog]

Hi AlphaFrog,
Sorry to bring out this post again! I have tried the WORDIF and CHARIF functions and it works great, but not sure if I can use vba to do the following in a Sub:

String1: Hi John, this is Elaine, how was your day?
String2: Hi John. This is Elaine, was day?

The program should be able to highlight the "," after John in String1, highlight the "t" in the word "this" in String1, and show the strikeout effect on the word "how" and "your" in String1, to show the differences String2 has against String1.
Not sure if this is possible, please enlighten me on this thanks!

A User Defined Function (UDF) like WORDIF or CHARDIF cannot affect the text formatting of its result. A macro could be created to compare two columns of data and output a column of formatted results, but that would go beyond the scope of this thread which is centered around the UDF functions. It would be best if you started a new thread for a request like that.