Excel Compare Two Strings for Similarity (3 Easy Ways)

You may need to compare two strings to complete your task. In Excel, you can do that easily. Today we are going to show you how to compare two strings for similarity in Excel. For conducting the session, we are using Excel 2019. You can use your preferred version.

First things first, let’s get to know about today’s practice workbook.

Excel sheet - Excel Compare Two Strings for Similarity

We have a basic table that contains several athletes’ names and the name they are popular among the mass (Known As). Both the columns have text strings. We will show how to compare them to find similarities.

It’s a basic dataset to keep examples simple, in practical life, you may encounter a much larger and complex data set.

Practice Workbook

You are welcome to download the practice workbook from the link below.

Compare Two Strings for Similarity

In Excel, we have several options to compare strings. We will show you the methods with basic examples for a better understanding.

1. Excel Tools

Excel offers several built-in features and tools that may help you in different circumstances. String comparison can be one of these circumstances.

Select the cells you want to compare, then explore the Home tab. You will find the Conditional Formatting option there. You will find the Highlight Cells Rules there.

Explore there, you will find the Duplicate Values option. Click that.

Duplicate values - Excel Compare Two Strings for Similarity

A dialog box will pop up in front of you. You can select what type of values you want to see and the format.

Dialog Box - Excel Compare Two Strings for Similarity

Here our selection is for duplicates. And the format is to fill with light red color.

Please note that you can choose Duplicate or Unique. 

Unique or Duplicate - Excel Compare Two Strings for Similarity

You can select any of these two depending upon your needs.

There are several predefined formats there.

Select format dialog box - Excel Compare Two Strings for Similarity

Choose your preferred option, and click OK. 

Result from Excel tools - Excel Compare Two Strings for Similarity

Here we have selected the Duplicate and fill with red light.

You can do the same, approaching it in some other way.

From the Conditional Formatting, you will find an option called New Rule. 

New Rule - Excel Compare Two Strings for Similarity

A new dialog box will appear in front of you.

New Formatting Dialog box - Excel Compare Two Strings for Similarity

Select any of the rules from here and then click Format.

Format cells - Excel Compare Two Strings for Similarity

Select your desired formation from here. And click OK 

Result - Excel Compare Two Strings for Similarity

2. Excel Functions

You can find the similarity between strings using Excel functions. There are several functions that may help you.

Let’s imagine a scenario, where we are comparing strings to find whether they match or not.

Match scenario - Excel Compare Two Strings for Similarity

I. Direct Logical Match

Before using any function let’s show you a simple way of comparing two strings. In this direct logical match option, all you need to do is to compare the two strings using the Equal sign. 

String1 = String2 

Write in the Excel.

Direct Logic - Excel Compare Two Strings for Similarity

Here we have compared the Known As to the Athlete Name using the equal sign. And since the two are not exactly the same it returned FALSE.

Do the same for the rest of the rows.

AutoFill results - Excel Compare Two Strings for Similarity

Here, Bruno Fernandes was found the same in both of the strings, so the result is TRUE. 

II. Using EXACT Function

You can use the EXACT function to compare two strings.

The EXACT function compares two text strings and returns TRUE if they are the same, and FALSE if not. EXACT is case-sensitive

EXACT (text1, text2)

text1: The first text string to compare.

text2: The second text string to compare.

To know more about the function visit the Microsoft Support site.

Write the function in Excel.

EXACT - Excel Compare Two Strings for Similarity

Here we have inserted the strings inside the EXACT function. Since the two strings are not the same the result is FALSE.

Write the function for the rest of the rows or exercise the Excel Autofill.

AutoFill EXACT - Excel Compare Two Strings for Similarity

III. Using SEARCH Function

Another function we can use is SEARCH. This function returns the location of one text string inside another.

The syntax for the SEARCH function is

SEARCH (find_text, within_text, [start_num])

find_text: The text to find.

within_text: The text to search within.

start_num: Starting position in the text to search. This is an optional field, the default value is 1.

SEARCH returns the position of the first character of find_text inside within_text. The SEARCH function is not case-sensitive.

To know more about the function visit the Microsoft Support site.

Since it returns the position, this function may not return what we are looking for. We need to use the IF function as well.

Within the IF function, we will check the logic as to whether the find_text is in the within_text or not. If so we will return the if_true_value here.

IF (SEARCH (find_text, within_text),if_true_value)

You may need to set an if_false_value as well. SEARCH starts its operation assuming that the find_text will be inside the within_text. When it finds that the find_text is not in the within_text, it returns an Error. 

So, we will set our if_false_value using the IFERROR function.

IFERROR (IF (SEARCH (find_text, within_text),if_true_value), if_false_value)

Write the formula in Excel.

SEARCH -Excel Compare Two Strings for Similarity

Here we have set the Known As column value as the find_text and the Athlete Name as the within_text. The if_true_value is set to “Similar” and the if_false_value is set to “Not Similar”.

The formula found the find_text inside the within_text, so as result, it returned “Similar” here.

Write the formula for the rest of the values or exercise the Excel Autofill feature.   

Autofill SEARCH - Excel Compare Two Strings for Similarity

Here all our listed values in the Known As column is in the Athlete Name, that’s why we have found similarities for all of them.

Let’s change any of the values a bit.

Example change - Excel Compare Two Strings for Similarity

Here we have intentionally misspelled Maradona to Mradona and our formula found the dissimilarity and returned Not Similar. 

You can use the FIND function in place of the SEARCH function. FIND is a case-sensitive function, apart from that, its function is similar to SEARCH. 

3. VBA Code

Apart from predefined functions and formulas, you can write your own formula in Excel to complete your task.

In the Developer tab, you will find an option called Visual Basic under the Code section.

Visual Basic - Excel Compare Two Strings for Similarity

Click that. You can use the Keyboard shortcut ALT + F11. 

This will open the Microsoft Visual Basic for Applications window.

Module - Excel Compare Two Strings for Similarity

Now in the Microsoft Visual Basic for Applications window, click Insert > Module.

Copy the code and paste it into the 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:", "Select Range", 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, "Similar or Not"

        GoTo lOne

    End If

lTwo:

    Set xRg2 = Application.InputBox("Range B:", "Select Range", "", , , , , 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, "Similar or Not"

        GoTo lTwo

    End If

    If xRg1.CountLarge <> xRg2.CountLarge Then

       MsgBox "Two selected ranges must have the same numbers of cells ", vbInformation, "Similar or Not"

       GoTo lTwo

    End If

    xDiffs = (MsgBox("Click Yes to highlight similarities, click No to highlight differences ", vbYesNo + vbQuestion, "Similar or Not") = 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

Code copy - Excel Compare Two Strings for Similarity

Now run the code by clicking the run icon or pressing the F5 key. It will ask for a range with which you need to check the similarities.

Select range - Excel Compare Two Strings for Similarity

Here we have selected the Known As column as Range A. And click OK. 

Now you will be asked for second string range.

Select Range 2 - Excel Compare Two Strings for Similarity

Insert the second range and click OK.  

Similar or Not - Excel Compare Two Strings for Similarity

This will ask your choice for Yes or No. Click Yes for similarities.

VBA Code Result - Excel Compare Two Strings for Similarity

This will highlight similar strings.

Conclusion

That’s all for today. We have tried listing several ways to compare two strings for similarity. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any approaches that we might have overlooked here.

shakil

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo