Excel Find Similar Text in Two Columns

Depending on the circumstances you may need to find similar text within two columns in Excel. Today, we are going to show you how to find similar text in two columns in Excel. For conducting the session, we are using Excel 2019. Feel free to use your version.

Before diving into the big picture, let’s get to know about the Excel book which is going to be our base of examples for today.

Excel sheet - Excel Find Similar Text In Two Columns

To keep things simple we have selected a basic table of two columns; Student Name and Summary. We will find the similarity between the elements of the columns.

This is a basic scenario to show you examples, in practical life, you may encounter many complex scenarios as well as a large data set.

Practice Workbook

You are welcome to download the workbook from the link below

Useful Text Functions

1. SEARCH 

The SEARCH function returns the location of one text string inside another. The syntax for the SEARCH function

SEARCH (find_text, within_text, [start_num])

find_text: The text to find.

within_text: The text to search within.

start_num: Starting position in the text to search. This is an optional field, defaults to 1.

SEARCH returns the position of the first character of find_text inside within_text. 

We will leave the Microsoft Support site link for you to know more about the function.

2. UPPER

The UPPER function converts a text string to all uppercase letters.

UPPER (text)

text: The text that you want to convert to uppercase

The UPPER function doesn’t affect the numbers, punctuation, and spaces.

To know more about the function visit the Microsoft Support site.

3. LOWER

The LOWER function converts a text string to all uppercase letters.

LOWER (text)

text: The text that you want to convert to lowercase

The LOWER function doesn’t affect the numbers, punctuation, and spaces.

To know more about the function visit the Microsoft Support site.

Find Similar Text In Two Columns

1. Finding Matches with SEARCH Function

While finding text that is similar to the measuring text you need to keep in mind that it may not be exactly the same at times. There can be differences between them. The most frequent differences we have noticed among the texts are case sensibility.

For you, word Play and play can be the same thing. Yes, they are almost the same, the only difference is the case. These two are similar for computing tools.

To find the similarities we can take the help of the SEARCH function in Excel.

Let’s imagine a scenario, where you need to check whether a particular text matches the text from another column.

Match/Mismatch column - Excel Find Similar Text In Two Columns

Here we have two columns Student Name and Summary. We will check whether there are similarities between the elements of these two columns.

Our formula will be something like

IFERROR(IF(SEARCH(find_text,within_text),true_value),error_value)

You may think why we are using the IFERROR function?

The SEARCH function returns the position when starting off the text once matches are found, but when no match? Then the SEARCH function returns an error value.

To eradicate that error we are using the IFERROR function here. And the error_value here will be the value if we don’t find matches, simply the false_value. 

Write the formula in Excel.

Formula SEARCH - Excel Find Similar Text In Two Columns

Here within the SEARCH function, we have inserted the find_text (Student Name) and the within_text (Summary). 

This SEARCH function is within an IF function. Once the SEARCH function finds matches it becomes TRUE, we have set the true_value to Match. So, when it finds a similar value, Excel will show Match as result.

And we have already discussed the usefulness of the IFERROR function. We have set our if_false value Mismatch using this function.

Formula result - Excel Find Similar Text In Two Columns

The formula returns the true_value here since we have a match.

Example of similar -Excel Find Similar Text In Two Columns

You can see, though in the Summary column it was written ‘alan’  instead of Alan, the formula produces the resulting Match. 

Write the formula for the rest of the rows here.

Fill rows - Excel Find Similar Text In Two Columns

The highlighted cells were not in the match so the result here is Mismatch.

2. Extracting The Similar Text

You may need to extract a similar text once you have known that it will be found in that column.

Let’s have a scenario, where you need to find and fetch a similar text.

Scenario to extract - Excel Find Similar Text In Two Columns

We are adapting the scenario using the previous example. In addition, we are going to fetch the text that has been matched.

To extract the text we have an Excel function called LEFT. 

The LEFT function extracts a given number of characters from the left side of a supplied text string.

LEFT (text, [num_chars])

text: The text from which to extract characters.

num_chars: The number of characters to extract, starting on the left side of the text. This is an optional field. If you don’t provide it will assume 1 by default.

In our scenario, at first, we need to check whether there is a match or not. For that, the IF function will be used.

The formula will be

IF(logic_check for match, LEFT(text,length),if_false_value)

Write the formula in Excel.

Extract -Excel Find Similar Text In Two Columns

First, we check whether there is a match or not using the Match/Mismatch column.

Then, within the LEFT function, we have set the text from which we need to extract the matching text and the length.

We use the LEN function, which provides the length of a given text. Within this, we have set the length of our desired text, so that Excel can understand how much to be extracted.

Extract similar text - Excel Find Similar Text In Two Columns

It gave us the text as the final result.

Write the formula for the rest of the rows.

Fill with formula - Excel Find Similar Text In Two Columns

Apart from the Mismatch row, it provided the text. And for the Mismatch row, the outcome is Not available. 

Keep in mind, here our text was on the left side so we can use the LEFT function without any hesitation.

But your text could be somewhere within a long string. You need to find the position of the desired similar text and start extracting from there. To do so, the MID function will be helpful.

MID (text, start_num, num_chars)

text: The text to extract from.

start_num: The location of the first character to extract.

num_chars: The number of characters to extract.

The formula will be similar to the previous one, the only change will be, within the MID function we need to tell the starting position of the text.

To state the starting position, we will use the SEARCH function.

Write the formula in Excel.

MID function to extract - Excel Find Similar Text In Two Columns

Within the MID function, we have inserted the text, then the starting position using the SEARCH function, then the length. The procedure is the same as the previous one. And we have found the text.

Example MID - Excel Find Similar Text In Two Columns

We have changed the Summary string a bit for Lord. The similar text at the end. Though our formula fetches the text.

You can choose the LEFT, RIGHT, or MID considering your demand.

3. Finding value using Similar Text Matches

You may need to find value using a similar text. There can be many circumstances, to keep things simple let’s imagine a circumstance where you are given a student list and the scholarship holders list.

Scenario students - Excel Find Similar Text In Two Columns

The scholarship holder list consists of the Student ID. You can see a couple of ids are typed differently compared to the original id.

Now we need to find the name of the student using these ids.

Scenario Name find - Excel Find Similar Text In Two Columns

Our task will be done using the INDEX-MATCH functions.

INDEX(find_array,MATCH(lookup_value,lookup_array, 0))

The MATCH function is used to locate the position of a lookup value in a row, column, or table.

The INDEX function returns the value at a given location in a range or array.

Write the formula in Excel.

INDEX - MATCH - Excel Find Similar Text In Two Columns

Here inside the INDEX function, we set the Student Name column as the find_array.  The MATCH function for declaring the row number.

Within the MATCH function, we have inserted the lookup_value and lookup_array using the UPPER function. So that the similarities can be overcome. 0 for stating Exact Match. 

Use CTRL + SHIFT + ENTER to execute the formula since it’s an array formula.

Result of INDEX-MATCH - Excel Find Similar Text In Two Columns

You can use the LOWER function as well instead of the UPPER function.

The UPPER function would compare after converting the text into upper-case and the LOWER function would compare converting into lower-case.

LOWER function - Excel Find Similar Text In Two Columns

Using the LOWER function we have derived the answer here.

You can use the VLOOKUP function instead of the INDEX-MATCH function.

The VLOOKUP is an Excel function to look up data in a table organized vertically. Since we are dealing with columns we can use this function.

VLOOKUP(lookup_value,lookup_array,column_number)

Write the formula in Excel.

VLOOKUP- Excel Find Similar Text In Two Columns

Here we have set the lookup_value using the UPPER function, then the lookup_array and 2 is the column number, as the Student Name is in the second column of our lookup_array. 

You can use the LOWER function here as well instead of the UPPER. 

Now write any of the formulas for the rest of the rows.

Fill rows - Excel Find Similar Text In Two Columns

Conclusion

That’s all for today. We have tried listing the ways of finding similar text in two columns in Excel. Hope you will find this helpful. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here.

shakil

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo