Finding similar text in two columns in Excel means finding exactly the same or partially same text from 2 columns.
In this Excel tutorial, you will learn how to find similar text in two columns in Excel.
Let’s see an example. In the following image, you see two columns having similar names. Column E shows which names are exactly the same and which are not.
In this article, you will learn how to
- Find the same text in 2 columns side by side or in whole columns.
- Check if two columns have the same first or last words in their text string.
- Find the similarity of text in two columns by matching the first or last N characters.
- Find and extract words with the same prefix or suffix.
- Use VBA to similar text in two columns and highlight them.
Lastly, we have shown how to use the Find command to find similar text in the whole worksheet or workbook.
1. Find Exactly Same Text in Two Columns by IF Function (Compare Side by Side)
Here, I will show you how to find whether 2 side by side cells in 2 columns have exactly the same text. We have used the IF function here.
The IF function has the following syntax:
=IF(logical_test, [value_if_true], [value_if_false])
The IF function returns a value when the logical test is TRUE and returns another value when the logical test is FALSE.
Let’s say, I have some names in 2 columns (B and C). I will compare B6 with C6, B7 with C7 and so on. If B6=C6, I will comment “Same” in cell E6. If B6≠C6, I will comment “Not Same”.
Follow these steps:
Step 1: Input this formula in cell E6:
=IF(B6=C6,"Same","Not Same")
Step 2: Press Enter and you will get the following output.
Ross Taylor and Ross Tailor do not match because Tailor and Taylor are not the same in spelling.
Now, hover your mouse over the bottom right corner of cell E6, and you will find the Fill Handle icon.
Step 3: Double-click on the Fill Handle icon and this will copy the formula for the rest of the cells.
Finally, you get 3 adjacent cells of the 2 columns that have the same text.
What if the cells with the same names are not in side-by-side positions? How to find the matching cells in such a case? In the next section, I will answer this question.
Read More: Formula to Find Duplicates in Excel
2. Check If a Text Is Found in Any of the Two Columns by Using Conditional Formatting
Conditional formatting is a method to change the formatting of cells based on some conditions given. Say, you have an Excel worksheet with some text in some cells. If you want to format the cells with a certain color that has a certain text,
– Here the condition part is: If the cells contain a text
– And the format part is: Fill those cells with a specified color
To check if a text is found in any of the 2 columns and highlight the cells with a color, let’s first set criteria in cell E6.
Step 1: In cell E6, I input a name, e.g., John Doe.
Then I will highlight the cells in two columns that have this name.
Step 2: Select all the values (B6:B15) of the first column ⇒ go to the Home tab ⇒ Conditional Formatting drop-down ⇒ New Rule option.
The New Formatting Rule dialog box will open.
Step 3: In the New Formatting Rule dialog box, select Use a formula to determine which cells to format ⇒ put the following formula in the formula box:
$B6=$E$6
Step 4: Click on the Format button and the Format Cells dialog box will open.
Go to Fill tab ⇒ choose a Background Color ⇒ press OK.
Step 5: Now the New Formatting Rule dialog box will open again. Click on the OK button to apply the changes.
Step 6: Follow the same process to apply the Conditional Formatting in the 2nd column.
Now, if we select the name John Doe from the drop-down, the names present in both columns will be highlighted with a light green color.
Read More: How to Find Duplicate Values Using VLOOKUP in Excel
3. Check If Two Columns Have Partially Same Text (Same First/Last Word)
In this section, I will show you how to find the texts in two columns that have the same first or last word.
For example, Ross Taylor and Ross Tailor– are not the same. But their first names have the same spelling.
Robwrt Johnson and Robert Johnson– both have the same last names.
To match the first or last words in 2 columns, we have to use the LEFT and FIND functions (for 1st word) or RIGHT and FIND functions (for the last word).
Case 1: Check for Same First Word
Here, we’ll check if the adjacent cells of the 2 columns have the same first name. For that, we will design a formula with the IF, LEFT, and FIND functions. LEFT and FIND functions will be used twice in the formula.
The LEFT and FIND functions will return the first name from 2 full names. Then the IF function will match them. If the names match, the IF function will return “Same”, otherwise, “Not Same”.
Follow these steps:
Step 1: In cell E6, input this formula:
=IF(LEFT(B6, FIND(" ", B6)-1)=LEFT(C6, FIND(" ", C6)-1),"Same","Not Same")
Step 2: Press Enter and then copy the formula down to all cells.
You can see now, which of the names side by side have the same first name..
For your easy understanding, we have used conditional formatting to distinguish the matched and mismatched names.
Case 2: Check for Same Last Word
Here, we’ll check if the adjacent cells of the 2 columns have the same last name. We’ll use the RIGHT and LEN functions. Moreover, to find the last word, you have to combine the SUBSTITUTE function with RIGHT and LEN.
Follow these steps:
Step 1: In cell E6, input this formula:
=IF(RIGHT(B6,LEN(B6)-FIND("^",SUBSTITUTE(B6," ","^",LEN(B6)-LEN(SUBSTITUTE(B6,"","")))))=RIGHT(C6,LEN(C6)-FIND("^",SUBSTITUTE(C6," ","^",LEN(C6)-LEN(SUBSTITUTE(C6," ",""))))),"Same","Not Same")
Here, RIGHT(B6,LEN(B6)-FIND(“^”,SUBSTITUTE(B6,” “,”^”,LEN(B6)-LEN(SUBSTITUTE(B6,””,””)))), this part returns the last names from cell B6 and other part returns the last name from cell C6.
If the last name of cell B6=last name of cell C6. then the IF function will return “Same”, otherwise, “Not Same”.
Step 2: Copy the formula down to all cells and you find all the cells of the 2 columns that have the same last name.
Read More: How to Find Duplicates without Deleting in Excel
4. Find Similar Text by Matching First/Last N Characters in Two List
Similar text may also mean that their first N or last N characters are the same. This section shows you how to find similar text by matching the first or last N characters in 2 columns.
To match the characters, we will use IFERROR and SEARCH functions. We’ll combine LEFT function with them (IFERROR and SEARCH) to match the first characters. To match the last characters, we will use RIGHT function instead of LEFT.
Case 1: Match First N Characters
Consider the following dataset. We can see 2 lists of words here. We want to know, which of the words have the same N first characters. This comparison is side by side, i.e., 2 adjacent cells in the same row.
Follow these steps:
Step 1: Put the number in cell E7. This refers to how many characters you want to match. Here, we have put 3.
Step 2: So, in cell E10, input this formula:
=IF($E$7="","",IFERROR(IF(SEARCH(LEFT(B10,$E$7),C10),"Same"),"Not Same"))
Step 3: Copy the formula down.
After copying the formula in all cells, you’ll find all the rows where the first 3 characters are the same.
Case 2: Match Last N Characters
Here, we’ll match the last N characters of the adjacent cells of the 2 columns. So we’ll replace the LEFT function with the RIGHT function here. The rest parts of the formula will be the same.
The formula is:
=IF($E$7="","",IFERROR(IF(SEARCH(RIGHT(B10,$E$7),C10),"Same"),"Not Same"))
Read More: Excel Formula to Find Duplicates in One Column
5. Find and Extract Words with Same Prefix or Suffix
Sometimes you may need to find and extract words with the same prefix or suffix. We can do this by using the FILTER, TOCOL and other Excel functions.
Note 1: The TOCOL function is only available in Excel for 365. It converts an array to a single column.
Note 2: The FILTER function is available from Excel 2019.
Case 1: Extract Words with Same Prefix
Here, I’ll extract words with the same prefix from 2 columns. So we’ll combine the IF, FILTER, TOCOL, ISNUMBER, SEARCH, and LEFT functions.
I have the following dataset with the list of words in 2 columns.
Follow these steps:
Step 1: Choose a prefix from the drop-down (we choose de) and in cell E10, input this formula:
=IF(E7="","",FILTER(TOCOL(B10:C22),ISNUMBER(SEARCH(E7,LEFT(TOCOL(B10:C22),4)))))
Step 2: Press Enter and you’ll get all the words with the prefix de in a single column.
Case 2: Extract Words with Same Suffix
Here, I’ll extract words with the same suffix from 2 columns. So we’ll replace the LEFT function with the RIGHT function here. The rest parts of the formula will be the same.
Choose a prefix from the drop-down (tion) and in cell E10, input this formula:
=IF(E7="","",FILTER(TOCOL(B10:C22),ISNUMBER(SEARCH(E7,LEFT(TOCOL(B10:C22),4)))))
After pressing Enter, you’ll get all the words with the suffix tion in a single column.
Read More: Find and Highlight Duplicates in Excel
6. Use VBA to Find and Highlight Similar Text in Two Columns
In this part, we’ll show you how to compare 2 columns and match text of each column from the left. Then highlight the matched part of the 2nd string with a color. To do this, we have to use VBA macro.
To demonstrate this, we’ll use the same dataset we used in Example 1, 2 and 3.
Now, follow these steps:
Step 1: Go to the Developer tab ⇒ click on the Visual Basic option.
Or you can use the keyboard shortcut Alt + F11 to open the Visual Basic window directly.
The Visual Basic window will open.
Step 2: Go to the Insert tab ⇒ click on the Module option.
A new Module will open.
Step 3: Put the following code in the Module:
Sub highlight_similar_text()
On Error Resume Next
If ActiveWindow.RangeSelection.Count > 1 Then
Text = ActiveWindow.RangeSelection.AddressLocal
Else
Text = ActiveSheet.UsedRange.AddressLocal
End If
One:
Set Range1 = Application.InputBox("First Range:", "Exceldemy", Text, , , , , 8)
If Range1 Is Nothing Then Exit Sub
If Range1.Columns.Count > 1 Or Range1.Areas.Count > 1 Then
MsgBox "You select several columns ", vbInformation, "Exceldemy"
GoTo One
End If
Two:
Set Range2 = Application.InputBox("Second Range:", "Exceldemy", "", , , , , 8)
If Range2 Is Nothing Then Exit Sub
If Range2.Columns.Count > 1 Or Range2.Areas.Count > 1 Then
MsgBox "You select several columns ", vbInformation, "Exceldemy"
GoTo Two
End If
If Range1.CountLarge <> Range2.CountLarge Then
MsgBox "Selected ranges should be equal ", vbInformation, "Exceldemy"
GoTo Two
End If
Differ = (MsgBox("Select Yes for similar text, Select No for dissimilar text ", vbYesNo + vbQuestion, "Exceldemy") = vbNo)
Application.ScreenUpdating = False
Range2.Font.ColorIndex = xlAutomatic
For I = 1 To Range1.Count
Set FirstCell = Range1.Cells(I)
Set SecondCell = Range2.Cells(I)
If FirstCell.Value2 = SecondCell.Value2 Then
If Not Differ Then SecondCell.Font.Color = vbRed
Else
xLen = Len(FirstCell.Value2)
For J = 1 To xLen
If Not FirstCell.Characters(J, 1).Text = SecondCell.Characters(J, 1).Text Then Exit For
Next J
If Not Differ Then
If J <= Len(SecondCell.Value2) And J > 1 Then
SecondCell.Characters(1, J - 1).Font.Color = vbRed
End If
Else
If J <= Len(SecondCell.Value2) Then
SecondCell.Characters(J, Len(SecondCell.Value2) - J + 1).Font.Color = vbRed
End If
End If
End If
Next
Application.ScreenUpdating = True
End Sub
Step 4: Press the Run button to run the code and you’ll get the following input box. You can also apply the keyboard shortcut F5 to run the code.
Put the range of the first column in the box.
Step 5: Click OK and you’ll get another input box. Put the range of the second column in the box.
Step 6: Click OK and you’ll get the following dialog box.
Step 7:
Choose Yes to highlight the cells of the 2nd column having similarities.
Step 8: Select No to highlight the cells of the 2nd column having differences.
Read More: Find Duplicates in Two Columns in Excel
How to Use the Find Command to Find Similar Text in Two Columns in Excel
The Find feature of Excel helps to find similar text from all of the texts in the worksheet or whole workbook.
In this section, we’ll use the Find command to find similar text in our dataset.
You can perform both exact and partial match (with ampersand and question mark operator) through this Find command.
To get this feature, go to the Home tab ⇒ click on the Find & Select drop-down ⇒ select the Find command.
The Find and Replace dialog box will open. (The keyboard shortcut is Ctrl + F)
We want to find all the similar text matches with Ross. So, put this in the search box.
Click on the Find All button and you’ll get the result. You can see the texts and their position that match with the search result.
We can also do partial matching. Type R*T and you’ll get all the matching texts that have R in the starting position and T in the last position.
Type R?T and you’ll get only 2 matching texts that have R in the starting position and T in the last position and have space in between them.
If you click on any row of this list, it will take you to the respective cell.
Download Practice Workbook
This is how you can find similar text in two columns in Excel using different functions and features. You can find exactly similar text in two columns in Excel using the IF function. If you combine the IF function with some other functions you can do a partial matching. We have shown the use of the Conditional Formatting feature to highlight similar text in two columns. We have also shown how to find similar text in two columns in Excel by matching N characters and extracting words with the same prefix or suffix. The VBA is helpful to find and highlight similar or different characters in a text.
Lastly, we have shown the Find command to find similar text all over the worksheet.
If you know any other methods or face any problems that we have not discussed in this post, please share them with us in the comment box.
Related Articles
- Excel Find Duplicates in Column and Delete Row
- How to Find Duplicates in Excel Workbook
- How to Find Duplicates in Two Different Excel Workbooks
<< Go Back to Find Duplicates in Excel Column | Find Duplicates in Excel | Duplicates in Excel | Learn Excel