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.

**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.

- 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**

**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.

- 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.

**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**

- 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.

**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)**

- 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.

**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”)**

- 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.

**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**.

- 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**