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.
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.
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.
A dialog box will pop up in front of you. You can select what type of values you want to see and the format.
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.
You can select any of these two depending upon your needs.
There are several predefined formats there.
Choose your preferred option, and click OK.
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.
A new dialog box will appear in front of you.
Select any of the rules from here and then click Format.
Select your desired formation from here. And click OK
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.
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.
Write in the Excel.
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.
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
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.
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.
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
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.
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.
Write the formula in Excel.
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.
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.
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.
Click that. You can use the Keyboard shortcut ALT + F11.
This will open the Microsoft Visual Basic for Applications window.
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
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.
Here we have selected the Known As column as Range A. And click OK.
Now you will be asked for second string range.
Insert the second range and click OK.
This will ask your choice for Yes or No. Click Yes for similarities.
This will highlight similar strings.
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.