How to Find Similar Text in Two Columns in Excel (6 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.

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

To find similar texts in two columns in Excel:

1. Step_1: Insert a new column (Column C). 
2. Step_2: Then write the following formula in its first cell (C2): =IFERROR(IF(SEARCH(A2,B2),”Present”),”Absent”)
3. Now, press ENTER.
4. Then drag the Fill Handle down till the end of the column (C2:C10).

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.

To find the similar text in two columns in Excel using the IFERROR & SEARCH function, follow the steps below:

1. Insert a new column (Column C) adjacent to the main data table to show the results.
2. Write the following formula in its first cell (cell C2): =IFERROR(IF(SEARCH(A2,B2),”Present”),”Absent”)
3. Now press ENTER. 
4. 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 cell C2 to C10.

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

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

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

Use IF, MID & SEARCH Functions 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)

Here,

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

To use the IF, MID & SEARCH functions to find similar text in two columns, follow this guideline:

1. Create a new column (Column D) next to the dataset.
2. Then apply this formula in its top cell (D2): =IF(C2=”Present”,MID(B2,SEARCH(A2,B2),LEN(A2)),” “)
3. Now press ENTER.
4. Drag the Fill Handle down from cell 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.

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.

Find similar text in columns in Excel:

1. Go to the Home tab.
2. From the Editing group, click on the Find & Select drop-down list.
3. Select Find.
4. In the Find and Replace dialog box, write the text that you are looking for inside the Find What box.
5. Press the Find All button.

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

5+ Formulas to Find Duplicates in One Column in Excel

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

5 Ways to Find Matching Values in Two Worksheets in Excel

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.

Formula

=VLOOKUP(E4,$A$2:$B$13,2,0)

Formula Breakdown

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

Use the VLOOKUP function to find texts of two adjoining columns:

1. Write the following formula in the E4: =VLOOKUP(E4,$A$2:$B$13,2,0)
2. Then press the ENTER key.
3. Put the cursor over the Fill Handle and drag it down from cell E4 to E8.

The formula is now applied to the entire column.

4+ Methods to Filter Duplicate Values in Excel

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

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.

Find similar text in two columns using the TRUE and FALSE logical formula using these steps:

1. Step_1 : First, create a new column (Column C).
2. Step_ 2: Then, insert the following formula in its first cell (C2): =A2=B2
3. Press the ENTER key.
4. Then drag down the Fill Handle over the rest of the cells (C2:C13).

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

The outcome looks something like this:

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.