How to compare strings in Excel

Comparing strings in Excel refers to the process of checking if two or more text values are equal or not. When you compare strings in Excel, you are comparing the sequence of characters in two or more cells to determine if they match exactly or partially.

The example dataset below contains 2 lists of information about people from two different sources and we want to compare these strings for knowing that are they either exactly similar or not.

Case 1 – Case insensitive comparison of strings

The first case is related to comparing strings in which we don’t want to compare strings with exact similarity of uppercase or lowercase letters. For example, in the above dataset, we want to compare emails from List 1 and List 2. We know that emails are always case-insensitive; therefore, we can compare them using any formula without taking care of the case. Therefore, we will compare the email addresses as strings and check whether they are similar or not irrespective of their cases. So, now the simple steps described below will help you to compare strings accurately.

Step 1 – Selecting an appropriate place to compare

• Select a cell near the dataset where you want the results of similarity or difference to appear as shown below.

Step 2 – Create the formula using the IF function

• After selecting the cell, press = button on your keyboard.
• Write IF and select the IF function by pressing the tab button.
• Then write this formula (E2=E15,” Match Found”, “No Match”)

Where E2 and E15 are the cells to compare.

Match found is the text that would appear when the similarity is TRUE. 

No Match is the text that would appear when the similarity is FALSE.

• Then, close the parenthesis.
• Now, press Enter and the result will appear on your screen.

Step 3 – Implementing the function to all strings for comparison

• Select the cell in which you’ve implemented the formula.
• Move your cursor to the bottom right corner of the cell.
• Your cursor will turn into + shape. This is called the fill handle.
• Double-click the fill handle by using your mouse.
• Now, all the cells below will give you the results of the comparison.

Case 2 – Case sensitive comparison of strings

The second case is related to the comparison of strings in which we want to compare strings with exact similarity of uppercase or lowercase letters. For example, in the given dataset, we want to compare the User IDs of people from List 1 and List 2. We know that User IDs are always case-sensitive and therefore, we have to compare them using specific formulas so that our results are accurate based on exactly similar letters and their cases. Therefore, we will compare the User IDs as strings and will check if the User IDs are exactly similar or not. So, now the simple steps described below will help you to compare strings accurately.

Step 1 – Selecting an appropriate place to compare

• Find a cell near the dataset where you want the results of similarity or difference to appear.
• After doing that, move your cursor over a cell that you want to select.
• Press the left-click button on your keyboard to select the cell.

Step 2 – Using the EXACT function

• Now that you’ve selected the cell, press the = button on your keyboard.
• Write EXACT on your keyboard and select the EXACT Function by pressing the tab.
• Then, enter the name of the first cell which you want to compare with others e.g., it is cell F2 in our case.
• After that, write the name of the second cell which is to be compared with the first cell e ig., it is cell F15 in our case.
• Then, close the parenthesis. The final formula should look like =EXACT(F2, F15).
• Now, press Enter and the result of the comparison of strings would appear on your screen.

Step 3 – Implementing the function to all strings for comparison

• Select the cell in which you’ve implemented the formula.
• Move your cursor to the bottom right corner of the cell.
• Your cursor will turn into a + sign shape, which is called the fill handle.
• Double-click the fill handle by using your mouse.
• Now, all the cells below will give you the results of the comparison.