How to Find Similar Text in Two Columns in Excel (5 Ways)

We often need to find similar texts in two columns in Excel. Well, there are quite a few handy techniques to do that. I am going to discuss 5 different methods to identify similar texts or characters in two columns in Excel.

Quickest Way to Find Similar Text in Two Columns in Excel

To find similar texts in two columns,

1. First, insert a new column (Column C). 

2. Then write the following formula in its first cell (C2):

=IFERROR(IF(SEARCH(A2,B2),”Present”),”Absent”)

3. Now, press ENTER

4. Then put the mouse cursor on the Fill Handle icon and drag it down till the end of the column (C2:C10).

Quick Method to Find Similar Text in Two Columns in Excel

🔗 How to Find Duplicates in Two Columns in Excel (7 Methods)

Alternative Way #1. Use the IFERROR & SEARCH Function to Find Similar Text in Two Columns in Excel

Here is an example of a dataset regarding building facilities. I need to find if the texts of Column A are available in Column B or not.

To find the matching texts inside the two columns, I used the SEARCH function and IFERROR function.

The syntax here is,

=IFERROR(IF(SEARCH(find_text,within text)[true_value],[false_value])

Only the SEARCH function could do the work of finding the same texts. But that will show #VALUE! when matches won’t be found in the two adjacent cells.

So to get rid of this problem, I applied the IFERROR function here. This will show the True_values as Present, and False_values as Absent.

Usage Guide ↓

  • First, insert a new column adjacent to the main data table to show results.

I have created the new column in Column C and named it ‘Similar Text’.

  • Then write the following formula in its first cell (C2):
 =IFERROR(IF(SEARCH(A2,B2),"Present"),"Absent")

Use the IFERROR & SEARCH Function to Find Similar Text in Two Columns in Excel

  • Now press ENTER
  • Then put the cursor over the Fill Handle, the tiny ‘+’ sign at the right-bottom corner of the C2 cell, and drag it down from C2-C10.

Result of the IFERROR & SEARCH Function to Find Similar Text in Two Columns in Excel

The presence of similar texts resulted in Present, and the dissimilarities are termed Absent.

🔗 How to Compare Rows in Excel for Duplicates (7 Ways)

Alternative Way #2. Use IF, MID & SEARCH Function to Find Similar Text in Two Columns in Excel

If you are looking for a way that will pull out the exact same texts lying inside the adjacent columns, you can use this method.

For this occasion, I am using the previously shown example datasheet.

The IF function will check the presence and absence of similar texts. 

The syntax is,

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

As I have 3 columns, I will be using a MID function that will detect the matched words from the left column with the mid one, in terms of the right column.

Here, the MID function syntax is,

MID(text,start_number,number_characters)

Text: is the text string that has the texts you want to extract

Start_number: is the array of the first text.

Number_characters: defines the number of characters you want to draw out from the middle column. 

Usage Guide ↓

  • At first, create a new column next to the previous columns (Column D).
  • Then apply this formula in its top cell (D2):
=IF(C2="Present",MID(B2,SEARCH(A2,B2),LEN(A2))," ")

Use the IF, MID & SEARCH Function to Find Similar Text in Two Columns in Excel

  • Now press ENTER.
  • After that, put the cursor over the Fill Handle icon and drag it down from D2 to D10.

The exactly matched text strings will appear on the cells where they are available, and unmatched results will show as empty strings.

Outcome: IF, MID & SEARCH Function to Find Similar Text in Two Columns in Excel

🔗 5 Ways to Find Matching Values in Two Worksheets in Excel

Alternative Way #3. Use the FIND Command to Find Similar Text in Columns in Excel

It is a quick technique that can find out your demanded texts or characters from the entire datasheet. I am using a simple dataset as an example here. But no doubt, your worksheet will be way more complex in reality.

Usage Guide ↓

  • First, go to the Home tab.
  • Then, under the Editing group, click on Find & Select drop-down list.
  • Then select Find.

The Find and Replace dialog box will show up.

  • Write the text that you are looking for inside the Find What box.
  • Then press the Find All button.

Use the FIND Command to Find Similar Text in Two Columns in Excel

I searched for the word ‘Kiwi’ and all the text strings containing ‘kiwi’ come along in the extended part of the dialogue box.

Output of the Use of the FIND Command to Find Similar Text in Two Columns in Excel

🔗 5+ Formulas to Find Duplicates in One Column in Excel

🔗 Find, Highlight, and Remove Duplicates in Excel [Step-by-Step]

Alternative Way #4. Use the VLOOKUP Function to Find Texts of Two Adjoining Columns in a Separate Column in Excel 

This method will help you find similar texts in two distinct columns and place their adjoining values in another data column with its previous arrangement.

Usage Guide ↓

  • First, write the following formula in the E4:
=VLOOKUP(E4,$A$2:$B$13,2,0)

E4: is the lookup value.

$A$2:$B$13: is the table array.

2: is the column index number, which depends on the column headers.

0: is the range lookup.

  • Then press ENTER.
  • Then put the cursor over the Fill Handle and drag it down from E4 to E8.

Use the VLOOKUP Function to Find Texts of Two Adjoining Columns in a Separate Column in Excel 

The formula is now applied to the entire column.

Result of the Use of the VLOOKUP Function to Find Texts of Two Adjoining Columns in a Separate Column in Excel 

🔗 4+ Methods to Filter Duplicate Values in Excel

🔗 How to Remove Duplicates in Excel [13 + Different Methods]

Alternative Way #5. Use TRUE & FALSE Logical Formula to Find Similar Text in Two Columns in Excel

I am using a list of fruits from which I will be uncovering the same fruit names using the TRUE-FALSE logical formula.

  • First, create a new column (Column C).

I named it Results.

  • Then, insert the following formula in its first cell (C2):
=A2=B2

Apply - TRUE & FALSE Logical Formula to Find Similar Text in Two Columns in Excel

A2: is the first input of column A.

B2: is the first input of column B.

The=sign before the functions compares the two columns and finds out if they are equal or not.

  • After that, press ENTER.

If the cells got similar texts, the result is TRUE. If the cells mismatch, the result is FALSE.

  • Then drag down the Fill Handle over the rest of the cells (C2:C13).

The outcome looks something like this:

Output of the Use of the TRUE & FALSE Logical Formula to Find Similar Text in Two Columns in Excel

🔗 4+ Ways to Find Duplicates in a Column and Delete Rows in Excel

🔗 Vlookup for Duplicate Values and Return the Matches in Excel [8 Cases]

Conclusion

Microsoft Excel offers several other ways to easily find similar words in your worksheet. But I tried to cover the most useful ones in this article. If you have any suggestions and more techniques that I can add to this blog, do share them with me in the comment section. 

(Visited 4,079 times, 9 visits today)

Similar Posts

4 Comments

  1. Hi iam trying to apply this formula to compare the text in 2 columns using below formula but it was not showing anything,what i did wrong?
    =IFERROR(IF(SEARCH(A2,B2),”Present”),”Absent”)

    1. Hi Madhuri,
      I think you have directly copied the formula from this blog to your Excel sheet. If the case is so, I suggest you erase all the double inverted commas (“”) from the formula and manually insert them again using your own keyboard. I think that will resolve the formula syntax issue.
      Regards!

  2. Hoping yo can help. How can you do this if both tables include same part of the text that could be anywhere, star middle of end. Example : ” Today is Tuesday all day” and “Did you work or Tuesday or not”
    The common work is Tuesday but it could be anywhere
    I am trying to lookup between two files but the text entry is not always the same, so i need to find similar. None of the formulas seem to work, just lost for options here. Thank you

    1. Hi Radica,
      The following VBA code will solve your problem.

      Sub FindAndHighlightWordBetweenColumns()
      Dim rng As Range, cell As Range
      Dim searchWord As String
      searchWord = InputBox(“Enter the word you want to search for:”)
      Set rng = Range(“A1:B100”) ‘Change the range to match your data
      For Each cell In rng
      If InStr(cell.Value, searchWord) > 0 Then
      cell.Interior.Color = RGB(255, 0, 0)
      End If
      Next cell
      End Sub

      Here are the steps to use the VBA code I provided above:

      Step_1: Open the Excel file you want to search in.
      Step_2: Press “Alt+F11” to open the Visual Basic Editor (VBE).
      Step_3: In the VBE, go to “Insert” > “Module” to create a new module.
      Step_4: Copy and paste the VBA code I provided above into the new module.
      Step_5: Adjust the range in the code to match the data you are working with. For example, if your data is in columns A and B starting from row 2, you should change the range to “A2:B100”
      Step_6: Press “F5” to run the code or select “Run” > “Run Sub/UserForm” from the menu.
      Step_7: A prompt will appear asking you to enter the word you want to search for. Type the word and press “OK”.
      The code will search for the word in the specified range and highlight any cells containing the word with red color.
      You can also assign the code to a button if you want to run it multiple times.

      It’s important to keep in mind that the code will only look for the word inside the cells in the range specified in the code. If the word is not present in the range the code will not find it.

Leave a Reply

Your email address will not be published. Required fields are marked *