Tanzim
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.
Another Dataset is given for better understanding.
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.
- 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.
- 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
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.
- Second, on the ribbon, go to Home tab. click on Conditional Formatting.
- Then, choose New Rule from the drop-down menu.
- 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.
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.
- 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.
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.
- 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.
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.
- Then 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.
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.
- 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.
- 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.
📄 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
- 11 Approaches to Compare Addresses in Excel
- 5 Steps to Do Statistical Comparison of Two Data Sets in Excel
