Excel String Compare – 5 Easy Methods

(Note: This guide on Excel string compare is suitable for all Excel versions including Office 365)

Excel is a very reliable spreadsheet tool. In spite of this, errors can creep in due to human oversight. And some of these errors can make or break your database. 

Let us say, for example, you have a list of blacklisted contacts. Now, somebody from your sales team accidentally mixed up the list with good leads. What do you do now? 

While you can technically check the names one by one, it is practically impossible to compare hundreds of names manually. That’s why we need a fast but reliable way to compare strings in Excel. 

Fortunately, Excel has built-in features and functions that can easily compare strings. In this guide, let us explore them. 

You’ll learn:

1. Method 1: How to Compare Two Case Insensitive Strings? 
2. Method 2: How to Compare Two Case Sensitive Strings?
3. Method 3: How to Compare Two Strings Using VBA? 
4. Method 4: How to Compare Strings Using Conditional Formatting?
5. Method 5: How to Compare Strings for Similarity?

Related:

How to Remove Spaces in Excel? 3 Easy Methods

How to Insert Bullet Points in Excel? 5 Easy Methods

How to Set Print Area in Excel? Step-by-Step Guide

Method 1: How to Compare Two Case Insensitive Strings? 

To compare two strings, you can simply use the formula IF(A2=B2, “Equal”, “Error”) and drag it to the rest of the cells, provided you don’t care about the cases. 

If necessary, you can later filter similar strings and delete them. Please note that this “IF” function compares strings, numbers, dates, boolean expressions, etc. 

Method 2: How to Compare Two Case Sensitive Strings?

In case, you want to compare strings while accounting for different upper and lower case versions use the EXACT function. 

Let us see how to do this: 

1. In a separate column, enter the formula IF(EXACT(D2,E2), “Equal”, “Error”). 

2. This will return “Equal” if the values are exactly the same. Drag the formula to the entire range. 

Please note that the IF formula is optional and is only used for displaying a message instead of “TRUE” or “FALSE”. 

Also Read:

How to VLOOKUP From Another Sheet?

How to Move Rows in Excel? 5 Easy Methods

How to Fix the Excel Circular Reference Error?

Method 3: How to Compare Two Strings Using VBA? 

Well, the methods discussed above work only if you are comparing simple strings. What if you need to compare complex strings? 

Isn’t there any efficient way to compare strings in Excel? 

Fortunately, we have VBA. Using this VBA code, we can not only compare two strings but also highlight the differences between them. 

Let us see how to do this: 

1. Open the relevant Excel spreadsheet and press Alt+F11 to go to the VBA editor. 

2. Here, click on the Module option under Insert 

3. In the New Module window, paste the following VBA code and run it using the F5 shortcut. 

Sub highlight()
    Dim R1 As Range
    Dim R2 As Range
    Dim Txt As String
    Dim C1 As Range
    Dim C2 As Range
    Dim I As Long
    Dim J As Integer
    Dim xL As Integer
    Dim xD As Boolean
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
      Txt = ActiveWindow.RangeSelection.AddressLocal
    Else
      Txt = ActiveSheet.UsedRange.AddressLocal
    End If
lOne:
    Set R1 = Application.InputBox("Range A:", "Compare_Strings", Txt, , , , , 8)
    If R1 Is Nothing Then Exit Sub
    If R1.Columns.Count > 1 Or R1.Areas.Count > 1 Then
        MsgBox "Multiple ranges or columns have been selected ", vbInformation, "Compare_Stringsl"
        GoTo lOne
    End If
lTwo:
    Set R2 = Application.InputBox("Range B:", "Compare_Strings", "", , , , , 8)
    If R2 Is Nothing Then Exit Sub
    If R2.Columns.Count > 1 Or R2.Areas.Count > 1 Then
        MsgBox "Multiple ranges or columns have been selected ", vbInformation, "Compare_Strings"
        GoTo lTwo
    End If
    If R1.CountLarge <> R2.CountLarge Then
       MsgBox "Two selected ranges must have the same numbers of cells ", vbInformation, "Compare_Strings"
       GoTo lTwo
    End If
    xD = (MsgBox("Yes to Highlight Common String, No to highlight differences ", vbYesNo + vbQuestion, "Compare_Strings") = vbNo)
    Application.ScreenUpdating = False
    R2.Font.ColorIndex = xlAutomatic
    For I = 1 To R1.Count
        Set C1 = R1.Cells(I)
        Set C2 = R2.Cells(I)
        If C1.Value2 = C2.Value2 Then
            If Not xD Then C2.Font.Color = vbGreen
        Else
            xL = Len(C1.Value2)
            For J = 1 To xL
                If Not C1.Characters(J, 1).Text = C2.Characters(J, 1).Text Then Exit For
            Next J
            If Not xD Then
                If J <= Len(C2.Value2) And J > 1 Then
                    C2.Characters(1, J - 1).Font.Color = vbGreen
                End If
            Else
                If J <= Len(C2.Value2) Then
                    C2.Characters(J, Len(C2.Value2) - J + 1).Font.Color = vbRed
                End If
            End If
        End If
    Next
    Application.ScreenUpdating = True
End Sub

4. In the Compare_Strings dialog box, select the ranges of the strings you want to compare and click OK. 

5. Choose whether to highlight the differences or the similarities between the strings.  All differences will be highlighted in red and all similarities will be highlighted in green. 

Method 4: How to Compare Strings Using Conditional Formatting?

What if you are in a hurry and want to quickly compare strings for duplicates?

Well, you can take the help of the Conditional Formatting feature to instantly compare and highlight duplicates in Excel. 

To do this, just select the range of cells where you want to look for duplicates and go to Conditional Formatting > Highlight Cell Rules > Duplicate Values. This will highlight all duplicates in this range. 

Method 5: How to Compare Strings for Similarity? 

If you are looking to find similarities between two sets of un-identical strings, you can use this method. 

Just enter the formula IFERROR( IF (SEARCH (text1, text2),if_true),if_false) in an adjacent column and drag it to the entire range. In this example, we are comparing column D with column E. Hence the formula will be =IFERROR(IF(SEARCH(D2,E2),”Similar”),”Different”)

This will check if text1 is inside text2 or not and return “Similar” or “Different” accordingly. 

Suggested Reads:

How to Extract an Excel Substring? – 6 Best Methods

Excel Quick Analysis Tool – The Best Guide (5 Examples)

How to Lock Cells in Excel?— 4 Best Methods with Examples

Let’s Wrap Up

In this short guide, we discussed five easy methods to compare strings in Excel. Try each one out and use the one which suits you best. In fact, you can modify some of these methods to your liking. Please let me know if you have any questions in the comments section. 

We are always happy to help. 

Please visit our free resources section for more high-quality Excel guides.

Ready to upskill your Excel knowledge? Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 130+ training courses. Click here for advanced Excel courses with in-depth training modules.