6 Easy Methods to Compare Two Strings in Excel for Similarity

Users frequently need to compare the two strings in Excel for similarity. It’s quite straightforward and easy to compare two strings. You may simply accomplish this by using basic Excel formulas or VBA. I’ll demonstrate how to compare two strings for similarity in Excel with examples.


📁 Download Excel File

To prepare this article, we used an Excel workbook, which is available for download. Additionally, you can edit and customize the data you enter while viewing the results.


Learn to Compare Two Strings for Similarity in Excel Using These 6 Methods

We used a small dataset to clearly demonstrate the steps. There are roughly 17 rows with 2 columns in the dataset. We are starting with all cells with General format. We have two distinct columns for each dataset: Football Team and Cricket Team.

excel compare two strings for similarity dataset

Another Dataset is given for better understanding.

Dataset 2

Method 1

1. Applying Conditional Formatting for Duplicates

I’ll look to compare two strings in Excel for similarity in the whole set of data by using Conditional Formatting. See steps below for how to do that:

⬇️⬇️ STEPS ⬇️⬇️

  • First, choose the range of data B6:B22 to find duplicates in the whole set of data.

Applying Conditional Formatting for Duplicates

  • Second, choose Conditional Formatting again from Home tab. Then choose Highlight Cells Rules from the drop-down menu.
  • Then, choose Duplicate Values from the second drop-down menu.

  • Third, in the Duplicate Values dialog box, set the formatting rules and choose the text color and fill color for the final result.

Applying Conditional Formatting for Duplicates

  • In the end, you’ll see that the duplicate values in your cells are highlighted, like in the image below.

📕 Read More: How to Compare Two Cells in Different Sheets with Excel Formula

Method 2

2. Using New Rule Tool of Conditional Formatting

This time we will find duplicates in a different row by using the COUNTIF operator in New Rule. For that we will follow the steps below.

⬇️⬇️ STEPS ⬇️⬇️

  • First, choose the range of cells B6:C22 from the main data set.

Using New Rule Feature of Conditional Formatting

  • Second, on the ribbon, go to Home tab. click on Conditional Formatting.
  • Then, choose New Rule from the drop-down menu.

excel compare two strings for similarity Using New Rule Feature of Conditional Formatting

  • Third, you’ll see a box called “New Formatting Rule.”
  • In this case, to use a formula Under “Select a Rule Type,” choose “Format only unique or duplicate values”.
  • Then, put the duplicate in the drop-down box from Format all.

  • Then, after using the above formula, click the Format button to change the way the cells look.

  • After setting the criteria for highlighting, click OK to close the dialog box.
  • Last, you’ll be able to see the duplicates, which will be highlighted in the data set like in the image below.

Using New Rule Feature of Conditional Formatting

Method 3

3. Using Equal Condition

Using equal operator is another easy way to compare strings in Excel. You can do it by following these steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Make a new column for the results and add the formula for comparing each cell, as shown below.

=B6=C6

excel compare two strings for similarity Utilising Equal Function

  • Then drag down the formula.

  • As shown below, Excel will show the result as FALSE if the comparison doesn’t work, and TRUE if it does.

Utilising Equal Function for excel compare two strings for similarity

Method 4

4. Introducing EXACT Function

Using EXACT function is also an easy way to compare strings in Excel. Returns TRUE if two of these text strings are identical, FALSE otherwise when comparing two text strings. Although it ignores formatting discrepancies, EXACT is case-sensitive. You can do it by following these steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Make a new column for the results and add the formula for comparing each cell, as shown below.

=EXACT(B6,C6)

Introducing EXACT Function to compare strings

  • Then drag down the formula.

excel compare two strings for similarity utilizing EXACT function

  • As shown below, Excel will show the result as FALSE if the comparison doesn’t work, and TRUE if it does.

Introducing EXACT Function to excel compare two strings for similarity dataset

Method 5

5. Use of SEARCH Function

We can also use SEARCH Function to compare strings in Excel. The SEARCH function finds one text string within another text string and returns the number of the first character of the first text string. You can do it by following these steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Make a new column for the results and add the formula for comparing each cell, as shown below.

=IFERROR(IF(SEARCH(C6,B6),”Similar”),”Not Similar”)

excel compare two strings for similarity utilizing SEARCH function

  • Then drag down the formula.

Drag down the formula

  • As shown below, Excel will show result as Not Similar if the comparison doesn’t work, and Similar if it does.

excel compare two strings for similarity Using the SEARCH Function

Method 6

6. Executing VBA Code

Now we will use VBA to compare duplicate rows. Before talking about the method, first, we have enabled the Developer Tab on our Ribbons. You can follow the link to see the process of enabling the Developer Tab by clicking on it here.  Let’s follow the steps below:

⬇️⬇️ STEPS ⬇️⬇️

  • Let’s press Alt+ F11 to bring up the VBA You can also do this by going to Developer tab. Clicking on Visual Basic.

Using VBA

  • Then we choose Module from Insert from the menu. This will bring up the window for the VBA Module. Here is where we will put our code.

  • Now we type the code below:
Sub SimTwoStrings()
    Dim Rnge1 As Range
    Dim Rnge2 As Range
    Dim InputTxt As String
    Dim InpCell1 As Range
    Dim InpCell2 As Range
    Dim Slt1 As Long
    Dim Slt2 As Integer
    Dim StrLng As Integer
    Dim BoolSlt As Boolean
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
      InputTxt = ActiveWindow.RangeSelection.AddressLocal
    Else
      InputTxt = ActiveSheet.UsedRange.AddressLocal
    End If
lOne:
    Set Rnge1 = Application.InputBox("Range A:", "Select Range", InputTxt, , , , , 8)
    If Rnge1 Is Nothing Then Exit Sub
    If Rnge1.Columns.Count > 1 Or Rnge1.Areas.Count > 1 Then
        MsgBox "Multiple ranges or columns have been selected ", vbInformation, "Similar or Not"
        GoTo lOne
    End If
lTwo:
    Set Rnge2 = Application.InputBox("Range B:", "Select Range", "", , , , , 8)
    If Rnge2 Is Nothing Then Exit Sub
    If Rnge2.Columns.Count > 1 Or Rnge2.Areas.Count > 1 Then
        MsgBox "Multiple ranges or columns have been selected ", vbInformation, "Similar or Not"
        GoTo lTwo
    End If
    If Rnge1.CountLarge <> Rnge2.CountLarge Then
       MsgBox "Two selected ranges must have the same numbers of cells ", vbInformation, "Similar or Not"
       GoTo lTwo
    End If
    BoolSlt = (MsgBox("Click Yes to highlight similarities, click No to highlight differences ", vbYesNo + vbQuestion, "Similar or Not") = vbNo)
    Application.ScreenUpdating = False
    Rnge2.Font.ColorIndex = xlAutomatic
    For Slt1 = 1 To Rnge1.Count
        Set InpCell1 = Rnge1.Cells(Slt1)
        Set InpCell2 = Rnge2.Cells(Slt1)
        If InpCell1.Value2 = InpCell2.Value2 Then
            If Not BoolSlt Then InpCell2.Font.Color = vbRed
        Else
            StrLng = Len(InpCell1.Value2)
            For Slt2 = 1 To StrLng
                If Not InpCell1.Characters(Slt2, 1).Text = InpCell2.Characters(Slt2, 1).Text Then Exit For
            Next Slt2
            If Not BoolSlt Then
                If Slt2 <= Len(InpCell2.Value2) And Slt2 > 1 Then
                    InpCell2.Characters(1, Slt2 - 1).Font.Color = vbRed
                End If
            Else
                If Slt2 <= Len(InpCell2.Value2) Then
                    InpCell2.Characters(Slt2, Len(InpCell2.Value2) - Slt2 + 1).Font.Color = vbRed
                End If
            End If
        End If
    Next
    Application.ScreenUpdating = True
End Sub
  • Now, click on Macros in Developer tab and open the macro.

  • Now, click Run in Macro window after choosing the SimtwoStrings.

SimTwoStrings

  • Then, put first range in Select Range dialog box. Then click OK.

  • Then, choose second range. Click OK.

  • To confirm, press Yes.

  • So, VBA code highlighted the values that are the same.

excel compare two strings for similarity using VBA


📄  Important Notes

🖊️  In Excel, the ribbon is used to access various commands. You can change a lot of things in the options dialogue window, like the ribbon, formulas, proofing, saving, and so on. So the appearance of worksheets on your device could be different from ours.

🖊️  A practice workbook is given so that you can practice yourself.

🖊️  All these exercises and tutorials are done on Microsoft Office 365. Some functions may be unavailable in previous versions.

🖊️  At the end of the Excel file, there is a sheet where they can practice.


📝  Takeaways from This Article

📌  Reader will be able to compare two strings for similarity in Excel using the highlight cells rules feature.

📌  Readers can apply the New Rule feature.

📌  Readers can also use the equal operator to compare the two strings for similarity.

📌  Moreover, users can utilize the EXACT and SEARCH functions.

📌  Finally the readers will get familiar with VBA code to compare the two strings for similarity.


Conclusion

That concludes today’s session. These are the methods for comparing two strings for similarity in Excel easily. We are hopeful that this article will be extremely beneficial to you. Don’t forget to leave your thoughts and questions in the comments section and explore our website, ExcelDen, the best Excel solutions provider.


Related Articles

(Visited 90 times, 1 visits today)
Tanzim

Tanzim

My name's Tanzim. My articles are meant to help you learn more about how Microsoft Excel works and how to use its different features. I enjoy gaming and hope that some of my knowledge will help me provide you with some interesting articles despite some tiresome technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo