[Solved] Compare columns and output difference

[Becs716]

Hi,
I'm looking to create helper columns in my data that show the difference between two names.
I don't just want it to highlight the differences (all the data has differences), I want it to show what characters are different between the two cells.
E.g.


Context: Many of the differences are only because of a . in the name. I want to be able to exclude these when reviewing the data as I have 8000 rows to check!

Not sure if this is even possible so any other ideas also welcome.

Thanks!

 

[shamimarita]

Hello Becs716,

What you’re looking for is a bit beyond standard Excel formulas, but it’s possible! You want to compare two strings cell-by-cell, highlight exactly what’s different (not just whether they’re different), and ignore trivial changes like periods. Here’s a solution using a custom VBA function to do exactly that:

• Press ALT + F11 in Excel to open the VBA editor.
• Go to the Insert tab >> select Module to add a new module.
• Copy-paste the following VBA code.

Function TextDiff(oldText As String, newText As String, Optional ignoreChars As String = ".") As String
    Dim i As Long, maxLen As Long
    Dim diff As String
    maxLen = Application.WorksheetFunction.Max(Len(oldText), Len(newText))
    diff = ""
    
    For i = 1 To maxLen
        Dim oldChar As String, newChar As String
        oldChar = Mid(oldText, i, 1)
        newChar = Mid(newText, i, 1)
        
        If i > Len(oldText) Then oldChar = ""
        If i > Len(newText) Then newChar = ""
        
        If oldChar <> newChar Then
            If InStr(ignoreChars, oldChar) = 0 And InStr(ignoreChars, newChar) = 0 Then
                diff = diff & newChar
            End If
        End If
    Next i
    
    TextDiff = diff
End Function

How to Use:

• Suppose your "Old name" is in A2 and "New name" is in B2.
• In C2 (Difference - Old):

=TextDiff(A2, B2)

• In D2 (Difference - New):

=TextDiff(B2, A2)

The ignoreChars parameter lets you ignore periods (.) or other trivial characters.

Explanation:

• The function compares both strings character by character.
• When a difference is found, it collects the character from the "new" text (or old, depending on direction), unless that character is in your ignore list (e.g., a period).
• It then returns a string with all the differing characters, so you can easily spot the real changes.

 

[Becs716]

Thank you shamimarita, that worked perfectly and is exactly what I was looking for!
You've saved me hours!