How to Find Similar Text in Two Columns in Excel

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


Method 1 – Find Exactly Same Text in Two Columns by IF Function (Compare Side by Side)

The IF function has the following syntax:

=IF(logical_test, [value_if_true], [value_if_false])

The IF function returns the second argument when the logical test (first argument) is TRUE and returns the third argument when the logical test is FALSE.

We have some names in two columns (B and C). Let’s compare B6 with C6, B7 with C7 and so on. If B6=C6, we will comment “Same” in cell E6. If B6≠C6, let’s use “Not Same”.

Dataset to Find Similar Text in Two Columns

Follow these steps:

  • Input this formula in cell E6:
=IF(B6=C6,"Same","Not Same")

Putting IF function to Find Similar Text in Two Columns

  • Press Enter and you will get the output.

Ross Taylor and Ross Tailor do not match because Tailor and Taylor are not spelt the same.

Using Fill Handle to Find Similar Text in Two Columns

  • Hover your mouse over the bottom right corner of cell E6, and you will find the Fill Handle icon.
  • Double-click on the Fill Handle icon, which will copy the formula for the rest of the cells.

Showing highlighted Similar Text in Two Columns

Read More: Formula to Find Duplicates in Excel


Method 2 – Check If a Text Is Found in Any of the Two Columns by Using Conditional Formatting

Conditional formatting is changes the formatting of cells based on some condition(s). 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 two columns and highlight the cells with a color, let’s first set criteria in cell E6:

  • In cell E6, I input a name, e.g., John Doe.

Then we 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.
  • Select all the values (B6:B15) of the first column.
  • Go to the Home tab, click on the Conditional Formatting drop-down, and choose the New Rule option.
  • The New Formatting Rule dialog box will open.

Selecting New Rule option from Conditional Formatting menu

  • 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

  • Click on the Format button and the Format Cells dialog box will open.
  • Go to Fill tab and choose a Background Color.
  • Press OK.

Choosing highlighting color from Format Cells window

  • Click on the OK button in the New Formatting Rule dialog box to apply the changes.

Clicking OK from New Formatting Rule window

  • Repeat the same process to apply the Conditional Formatting in the second column.
  • 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


Method 3 – Check If Two Columns Have Partially Same Text (Same First/Last Word)

In this section, we 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 exactly the same, but their first names have the same spelling. The similar concept applies to 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 the first word) or RIGHT and FIND functions (for the last word).


Case 1 – Check for Same First Word

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

Follow these steps:

  • In cell E6, input this formula:
=IF(LEFT(B6, FIND(" ", B6)-1)=LEFT(C6, FIND(" ", C6)-1),"Same","Not Same")
  • Press Enter and then copy the formula down to all cells.

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

  • We have applied conditional formatting to distinguish the matched and mismatched names.

Case 2 – Check for Same Last Word

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

Follow these steps:

  • 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,””,””)))) returns the last names from cell B6 and other part returns the last name from cell C6.

If the last name of cell B6 equals the last name of cell C6, the IF function will return “Same.” Otherwise, it returns “Not Same”.

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

  • Press Enter and copy the formula down to all cells and you find all the cells of the two columns that have the same last name.

Read More: How to Find Duplicates without Deleting in Excel 


Method 4 – Find Similar Text by Matching First/Last N Characters in Two Lists

To match the characters, we will use IFERROR and SEARCH functions with the LEFT or RIGHT functions.

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

Case 1 – Match First N Characters

Consider the following dataset with two lists of words. We want to know whether pairs of words (i.e. one word from the first list and its corresponding word in the second) share the same N first characters.

Follow these steps:

  • Put the number in cell E7. This is how many characters you want to match. Here, we have put 3.

Putting number of first characters to match in two columns

  • In cell E10, input this formula:
=IF($E$7="","",IFERROR(IF(SEARCH(LEFT(B10,$E$7),C10),"Same"),"Not Same"))
  • 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. The rest of the setup is the same as in previous case.

Change the formula to:

=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


Method 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. You 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 onward.


Case 1 – Extract Words with Same Prefix

Let’s use the following dataset with the list of words in two 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:

  • Choose a prefix from the drop-down (we choose de). If you don’t make a drop-down, you can input manually.
  • 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

  • Press Enter and you’ll get all the words with the prefix de in a single column.

Case 2 – Extract Words with Same Suffix

  • The setup is similar for the first case, but the drop-down contains a list of suffixes (if you don’t make one, use manual input.
  • Choose a prefix.
  • 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 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 


Method 6 – Use VBA to Find and Highlight Similar Text in Two Columns

We’ll compare two columns and match text of each column from the left, then highlight the matched part of the 2nd string with a color. Let’s use the same dataset with names as before.

Dataset to Find Similar Text in Two Columns using VBA

Follow these steps:

  • Go to the Developer tab and click on the Visual Basic option. You can use the keyboard shortcut Alt + F11 to open the Visual Basic window directly.

Opening Visual Basic Window

  • Go to the Insert tab and click on the Module option. A new Module will open.

Inserting new Module

  • 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

  • Press the Run button to run the code and you’ll get an 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

  • 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

  • Click OK and you’ll get a confirmation dialog box.

Selecting Yes to highlight text with similarities

  • Choose Yes to highlight the similarities in the second column.

Showing highlighted Similar Text in Two Columns using VBA

  • Alternatively, select No to highlight the differences in the second column.

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

You can perform both exact and partial match (with ampersand and question mark operator) through this Find command:

  • Go to the Home tab, click on the Find & Select drop-down, and select the Find command. The Find and Replace dialog box will open. (The keyboard shortcut is Ctrl + F)

Using Find command to find Similar Text in Two Columns

  • We want to find all the similar text matches with Ross. So, put that 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

  • You 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 two 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


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
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

2 Comments
  1. The VBA method did nothing. nothing was highlighted despite having similarities and differences in the two columns. followed instructions exactly.

    • Hello SH,

      The VBA code is working perfectly. Similarities of two columns are highlighted in Red color.
      Here, I am uploading a video of VBA code:

      Video of VBA code Highlighting Similarities

      Kindly use the following code again:

      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 multiple ranges or 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 multiple ranges or columns ", vbInformation, "Exceldemy"
              GoTo Two
          End If
          If Range1.CountLarge <> Range2.CountLarge Then
             MsgBox "Ranges should have the same numbers of cells ", vbInformation, "Exceldemy"
             GoTo Two
          End If
          Differ = (MsgBox("Select Yes to highlight similarities, Select No to highlight differences ", 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

      Please click on Yes to highlight the similarities.

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo