How To Compare Two Strings For Similarity Or Highlight Differences In Excel?

In this article, we will learn how to compare two adjacent strings in excel for identifying differences or similarities. Two methods have been explained in this article as mentioned below.

Compare two strings for similarities using a formula.

Compare and highlight two strings for similarities or differences using VBA Code.

Compare Two Strings for Similarities using a Formula

Step 1 ? A sample data has been taken as shown below to compare the strings of two columns.

Step 2 ? Now, enter the following formula in Match Result column and drag the same till the last row to which data comparison is required and press enter.

=EXACT(A2, B2)

Note ? In the formula, A2 and B2 are the cells of the comparing strings. The FALSE result indicates that the compared strings are different, and the TRUE result depicts the similar strings?

Formula Syntax Description 

Compare and Highlight Two Strings for Similarities or Differences using VBA Code

Step 1 ? Press Alt+F11 keys from the keyboard and the Microsoft Visual Basic for Applications window will open.

The above editor can also be opened using the Developer's tab as shown below?

Step 2 ? In the Microsoft Visual Basic for Applications window, double click ThisWorkbook available in the Project panel.

Step 3 ? Now copy the below VBA code and enter the same in the ThisWorkbook (Code) window.

Sub highlight()
   Dim xRg1 As Range
   Dim xRg2 As Range
   Dim xTxt As String
   Dim xCell1 As Range
   Dim xCell2 As Range
   Dim I As Long
   Dim J As Integer
   Dim xLen As Integer
   Dim xDiffs As Boolean
   On Error Resume Next
   If ActiveWindow.RangeSelection.Count > 1 Then
     xTxt = ActiveWindow.RangeSelection.AddressLocal
   Else
     xTxt = ActiveSheet.UsedRange.AddressLocal
   End If
lOne:
   Set xRg1 = Application.InputBox("Range A:", "Kutools for Excel", xTxt, , , , , 8)
   If xRg1 Is Nothing Then Exit Sub
   If xRg1.Columns.Count > 1 Or xRg1.Areas.Count > 1 Then
      MsgBox "Multiple ranges or columns have been selected ", vbInformation, "Kutools for Excel"
      GoTo lOne
   End If
lTwo:
   Set xRg2 = Application.InputBox("Range B:", "Kutools for Excel", "", , , , , 8)
   If xRg2 Is Nothing Then Exit Sub
   If xRg2.Columns.Count > 1 Or xRg2.Areas.Count > 1 Then
      MsgBox "Multiple ranges or columns have been selected ", vbInformation, "Kutools for Excel"
      GoTo lTwo
   End If
   If xRg1.CountLarge <> xRg2.CountLarge Then
      MsgBox "Two selected ranges must have the same numbers of cells ", vbInformation, "Kutools for Excel"
      GoTo lTwo
   End If
   xDiffs = (MsgBox("Click Yes to highlight similarities, click No to highlight differences ", vbYesNo + vbQuestion, "Kutools for Excel") = vbNo)
   Application.ScreenUpdating = False
   xRg2.Font.ColorIndex = xlAutomatic
   For I = 1 To xRg1.Count
      Set xCell1 = xRg1.Cells(I)
      Set xCell2 = xRg2.Cells(I)
      If xCell1.Value2 = xCell2.Value2 Then
         If Not xDiffs Then xCell2.Font.Color = vbRed
      Else
         xLen = Len(xCell1.Value2)
         For J = 1 To xLen
            If Not xCell1.Characters(J, 1).Text = xCell2.Characters(J, 1).Text Then Exit For
         Next J
         If Not xDiffs Then
            If J <= Len(xCell2.Value2) And J > 1 Then
               xCell2.Characters(1, J - 1).Font.Color = vbRed
            End If
         Else
            If J <= Len(xCell2.Value2) Then
               xCell2.Characters(J, Len(xCell2.Value2) - J + 1).Font.Color = vbRed
            End If
         End If
      End If
   Next
   Application.ScreenUpdating = True
End Sub

Step 4 ? Once the code is entered, press Alt+Q keys in the Keyboard to close the Microsoft Visual Basic for Applications window.

Step 5 ? Next, save the file in the format as, Excel Macro?Enabled Workbook.

Step 6 ? Now press Alt+F8 to run the code. The below prompt will open?

Step 7 ? Select the macro name and click Run.

Step 8 ? The first Kutools for Excel dialog box will open. Here, select the first column of text strings that you need to compare, and then click the OK button.

Step 9 ? Next, the second Kutools for Excel dialog box opens to select the second column strings, and click the OK button.

Step 10 ? After that a new Kutools for Excel dialog box opens. Here. if you want to compare strings for similarities, then click Yes and if you want to highlight the differences of the strings, click No in the below screenshot.

Step 11 ? If selected Yes, the similar strings will be highlighted as following.

Step 12 ? If selected No, the different strings will be highlighted as following.

Conclusion

Hence, we have learned two methods to identify the different and similar strings in an excel data. Please note that, the VBA code is limited to identify some special characters only. For example, it cannot compare the strings containing apostrophe mark and exclamation mark.