How to Find Similar Text in Two Columns in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview to Find Similar Text in Two Columns Using Excel IF Function

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.

Note: We have used Excel for Microsoft 365 to prepare this article.

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

Dataset to Find Similar Text in Two Columns

Follow these steps:

Step 1: Input this formula in cell E6:

=IF(B6=C6,"Same","Not Same")

Putting IF function to Find Similar Text in Two Columns

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.

Using Fill Handle to Find Similar Text in Two Columns

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.

Showing highlighted Similar Text in Two Columns

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.

Dataset to Find Similar Text in Two Columns using Conditional Formatting

Note: Here, we created a Drop Down List for the criteria. You can choose different names from the drop-down list.

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.

Selecting New Rule option from Conditional Formatting menu

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

Putting Formula under Conditional Formatting New Rule

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.

Choosing highlighting color from Format Cells window

Step 5: Now the New Formatting Rule dialog box will open again. Click on the OK button to apply the changes.

Clicking OK from New Formatting Rule window

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.

Showing Similar Text in Two Columns highlighted with 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”.

Note: The FIND and LEFT functions are available from Excel 2007.

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.

Using IF, LEFT and FIND functions to Find Similar Text in Two Columns

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.

Note: The RIGHT, SUBSTITUTE and LEN functions are available from Excel 2007.

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

Using IF, RIGHT, LEN, SUBSTITUTE and FIND functions to Find Similar Text in Two Columns

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.

Note: The IFERROR and SEARCH functions are available from Excel 2007.

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.

Putting number of first characters to match in two columns

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.

Using IF, IFERROR, SEARCH and LEFT functions to Find Similar Text in Two Columns based on first 3 characters


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

Using IF, IFERROR, SEARCH and RIGHT functions to Find Similar Text in Two Columns based on last 3 characters

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.

Choosing prefix from drop-down list to Find Similar Text in Two Columns based on prefix

Note: Here, we created a Drop Down List for the prefix to match. You can choose different prefixes from the drop-down list.

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

Using IF, FILTER, TOCOL, ISNUMBER, SEARCH and LEFT functions to Find Similar Text in Two Columns based on prefix

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

Choosing suffix from drop-down list to Find Similar Text in Two Columns based on suffix

After pressing Enter, you’ll get all the words with the suffix tion in a single column.

Using IF, FILTER, TOCOL, ISNUMBER, SEARCH and RIGHT functions to Find Similar Text in Two Columns based on suffix

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.

Dataset to Find Similar Text in Two Columns using VBA

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.

Opening Visual Basic Window

The Visual Basic window will open.

Step 2: Go to the Insert tab ⇒ click on the Module option.

Inserting new Module

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

Putting code in the Visual Basic window

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.

Choosing first range for applying code

Step 5: Click OK and you’ll get another input box. Put the range of the second column in the box.

Choosing second range for applying code

Step 6: Click OK and you’ll get the following dialog box.

Selecting Yes to highlight text with similarities

Step 7:

Choose Yes to highlight the cells of the 2nd column having similarities.

Showing highlighted Similar Text in Two Columns using VBA

Step 8: Select No to highlight the cells of the 2nd column having differences.

Showing highlighted different Text in Two Columns using VBA

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.

Using Find command to find Similar Text in Two Columns

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.

Searching Ross in 2 columns

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.

Showing results for Ross

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.

Partially searching for R*T

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.

Partially searching for R?T

If you click on any row of this list, it will take you to the respective cell.

Clicking any search result to going to that 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


<< Go Back to Find Duplicates in Excel Column | Find Duplicates in Excel | Duplicates in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sajid Ahmed
Sajid Ahmed

Sajid Ahmed, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, assumes the position of an Excel & VBA Content Developer at ExcelDemy. A self-motivated individual, his profound interest in research and innovation aligns seamlessly with his passion for Excel. In this role, Sajid not only adeptly addresses challenges but also demonstrates enthusiasm and expertise in gracefully navigating complex situations. This underscores his steadfast commitment to consistently delivering exceptional content. His interests... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo