When we compare just two cells manually, it is not difficult. But it’s never easy to compare hundreds and thousands of text strings. Fortunately, MS Excel provides us with several functions and ways to perform this quite easily. In this article, I will demonstrate several methods to compare the text of two cells in Excel.
1. Using “Equal to” Operator to Compare Text Two Cells in Excel (Case Insensitive)
Let’s see how to compare two cells’ text using a simple formula. Here we will not consider the case-sensitive issue. Our only concern is to check only the values. For this method let’s consider a dataset of fruits. In the dataset, we will have two-column Fruit lists. Now our task is to match the names of the fruits and show their matched result.
📌 Steps:
- Enter the formula in Cell D5.
=B5=C5
- Copy down the formula up to D13.
Note:
This formula will not work for case-sensitive issues that is why if the text matches with values but they are not in the same letter it will show TRUE for that.
2. Applying Excel EXACT Function to Compare Two Cells’ Text (Case Sensitive)
In this section, we will see how to compare two cells of text where we will consider the exact match using the EXACT function. For this method let’s consider a dataset used before. Now our task is to compare the names of the fruits and show their exact matched result.
📌 Steps:
- Enter the formula in Cell D5.
=EXACT(B5,C5)
- Copy down the formula up to D13.
Observation:
If you observe the result you can see that the EXACT function is returning the result TRUE if and only if the whole text is fully matched. It is also case-sensitive.
Use of EXACT Function with IF to Get a Text Output:
Here we will additionally use the IF function with the EXACT function to show the conditional results. For this also we will use the same dataset above.
📌 Steps:
- Enter the formula in Cell D5.
=IF(EXACT(B5,C5),"Similar","Different")
Formula Explanation:
Here our inner function is EXACT which is going to find the exact match between two cells. Let’s see the IF functions syntax:
=IF (logical_test, [value_if_true], [value_if_false])
In the first portion, it takes the condition or criteria, then the value which will be printed if the result is true and then if the result is false.
We will print Similar if the two cells get matched and Different if they are not. That’s why the second and third argument is filled up with this value.
- Copy down the formula up to D13.
3. Inserting Excel IF Function to Compare Text of Two Cells (Not Case-Sensitive)
We can use only the IF function for finding matches. Again, let’s see the process using the same dataset.
📌 Steps:
- Enter the formula in Cell D5.
=IF(B5=C5,"Yes","No")
- Copy down the formula up to D13.
4. Comparing Text of Two Cells by String Length with Excel LEN Function
Let’s see how we can check if the two cells’ text has the same string length or not. Our concern will be the same length text, not the same text. Our dataset will be the same as above.
📌 Steps:
- Enter the formula in Cell D5.
=IF(LEN(B5)=LEN(C5), "Same", "Not Same")
Formula Explanation:
- First, we need to know the basic concepts of the LEN function.
- The syntax of this function is: LEN (text)
- This function is used to count the characters of any text or string. When we pass any text in this function then it will return the number of characters.
- LEN(B5) this part first counts the character of each cell from the first column and LEN(C5) for the second one.
- If the length is the same then it will print the “Same” and if not then “Not Same”.
- Copy down the formula up to D13.
5. Comparing Two Cells Containing Text That Have Unnecessary Spaces in Excel
Let’s see how we can check if the two cells’ text has the same string with unnecessary spaces in the front, middle, or end. Our concern will be to find out the same text after removing spaces. Our dataset will be the same as above.
📌 Steps:
- Enter the formula in Cell D5.
=TRIM(B5)=TRIM(C5)
Formula Explanation:
- First, we need to know the basic concepts of the TRIM function.
- The syntax of this function is: TRIM(text)
- This function is used to remove all spaces from a text string except for single spaces between words.
- TRIM(B5) this part removes unnecessary spaces from the cell expect single spaces between words and TRIM(C5) for the second one.
- After removing spaces if both are the same then it will print the “TRUE” and if not then “FALSE”.
- Copy down the formula up to D13.
6. Comparing Text Strings of Two Cells in Excel by Occurrences of a Specific Character
Sometimes we may need to compare cells where it will contain specific characters. In this part, we will see how to compare two cells by the Occurrence of a Specific Character. Let’s consider a dataset of products with their send ID and received ID. These ids are unique and should be matched with send and receive IDs. We want to make sure that each row contains an equal number of shipped and received items with that specific ID.
📌 Steps:
- Enter the formula in Cell E5.
=IF(LEN(C5)-LEN(SUBSTITUTE(C5, $B5,""))=LEN(D5)-LEN(SUBSTITUTE(D5,$B5,"")),"Same","Not Same")
Formula Explanation:
- Here additionally we have used the SUBSTITUTE function. Let’s see the fundamentals of this function.
- The syntax of this function is: SUBSTITUTE (text, old_text, new_text, [instance])
- These four arguments can be passed in the function’s parameter. Among them, the last one is optional.
text- The text to switch.
old_text- The text to substitute.
new_text- The text to substitute with.
instance- The instance to substitute. If not provided, all instances are replaced. This is optional. - SUBSTITUTE(B2, character_to_count,””) using this part we are replacing the unique identifier with nothing using the SUBSTITUTE function.
- Then using LEN(C5)-LEN(SUBSTITUTE(C5, $B5,””)) and LEN(D5)-LEN(SUBSTITUTE(D5, $B5,””)) we are calculating how many times the unique identifier appears in each cell. For this, get the string length without the unique identifier and subtract it from the total length of the string.
- Lastly, the IF function is used to make the results more meaningful for your users by showing the true or false results.
- Copy down the formula up to E10.
7. Highlighting Matches by Comparing Text from Two Cells in Excel
In this example, we will see how to compare text and highlight the matches. For this also we will use the same dataset used in method 4. For this example, we do not need any column to show any results.
📌 Steps:
- Select the entire dataset.
- Go to Conditional Formatting. You will find it under the Home tab.
- Select the New Rule option.
- Select the option marked 1.
- Enter the below formula in the marked box 2.
=$B5=$C5
- Or you can just select the two columns of the dataset.
- After that, click on the Format option.
- Go to the Fill tab.
- Select any color.
- Then press OK.
- Click on the OK button.
- See the matched data highlighted.
8. Comparing Text from Two Cells Partially in Excel (Not Case-Sensitive)
In terms of comparing two cells, sometimes we may consider partial matching. In this section, we will see comparing two cells’ text partially. There are lots of functions available in Excel to check parietal elements. But in this example, we will consider the RIGHT function.
Let’s consider this data table and we will find if the last 6 characters are matched the two cells.
📌 Steps:
- Enter the formula in Cell D5 and copy down the formula up to
=RIGHT(B5,5)=RIGHT(C5,5)
9. Finding Matched Text in Any Two Cells in Same Excel Row
Let’s have a dataset of three fruit lists. Now we will compare the cells one with each other and we get any two cells matched in the same row then it will be considered as matched.
📌 Steps:
- Enter the formula in Cell E5 and copy down the formula up to
=IF(OR(B5=C5,C5=D5,B5=D5),"Yes","No")
Formula Explanation:
- Here additionally we have used the OR function. Let’s see the syntax of this function: OR (logical1, [logical2], …)
- It can take two or more logic in its parameters.
logical1 -> The first requirement or logical value to decide.
logical2 -> This is optional. The second requirement or logical value to evaluate. - OR(B5=C5, C5=D5, B5=D5)
This portion decides if all the cells are equal or at least two are equal or not. If yes then the IF function decides the final value based on the OR function’s result.
10. Finding Unique and Matched Cells by Comparing Text of Multiple Cells
Here our task is to find the fruits which are unique and which are getting matched in the same row. For matching, we will consider at least two cell matches. If at least two cells match then it will be considered a Match otherwise Unique.
📌 Steps:
- Enter the formula in Cell E5 and copy down the formula up to
=IF(COUNTIF(C5:D5,B5)+(C5=D5)=0,"Unique","Match")
Formula Explanation:
- Here the COUNTIF function is used additionally.
- In this function both the arguments in the parameter are mandatory. Firstly, it takes the range of cells that will be counted. The second section takes the criteria which is the condition. Based on this condition the counting will be executed.
- By using COUNTIF(C5:D5,B5)+(C5=D5)=0 we are trying to find out if the row has matched or unique values. If the count is 0 then it is unique otherwise there is a matched value.
How to Compare One Cell with an Entire Column in Excel
Here, we have a dataset with one fruit list and a matching cell. Now we will compare the matching cell with the Fruit List column and find the match result.
📌 Steps:
- Enter the formula in Cell E5.
=$E$5=B5:B13
- After that, press the Enter button.
When Cell E5 matches with corresponding cells of Range B5:B13, then returns TRUE. Otherwise, returns FALSE.
Download Practice Workbook
Conclusion
These are the ways, we compare the text of two cells in Excel. I have shown all the methods with their respective examples but there can be many other iterations. Also, I have discussed the fundamentals of these functions and their most commonly used format codes. If you have any other method of achieving this then please feel free to share it with us.
<< Go Back To Excel Compare Cells | Compare in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
