# Excel Compare Two Cells Text (9 Examples) When we do compare two cells manually, it is not an issue. But it’s next too difficult to locate the variations between hundreds and thousands of text strings. In that way, MS Excel provides various functions and ways to do this type of task easily. In this article, I will show different methods to compare two cells’ text in Excel.

## 9 Ways to Compare Two Cells Text in Excel

### 1. Compare Two Cells Text Using Formula (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. Step 1: Enter the formula in cell D4.

`=B4=C4` Step 2: Copy down the formula up to D12 [Note: As this formula will not work for the case-sensitive issues that are why if the text matches with values but they are not in the same letter it will show TRUE for that.]

### 2. Compare Two Cells Text Using EXACT Function (Case Sensitive)

In this section, we will see how to compare two cells of text where we will be considered 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. Step 1: Enter the formula in cell D4.

`=EXACT(B4, C4)` Step 2: Copy down the formula up to D12 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. #### 2.1 EXACT with IF Condition

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.

Step 1: Enter the formula in cell D4.

`=IF(EXACT(B4,C4),"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.

As 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. Step 2: Copy down the formula up to D12 ### 3. Compare Two Cells Text Using IF Function

We can use only the IF function for finding matches. Again, let’s see the process using the same dataset.

Step 1: Enter the formula in cell D4

`=IF(B4=C4,"Yes","No")` Step 2: Copy down the formula up to D12 [Note: This is case insensitive]

### 4. Compare Two Cells Text by String Length

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. Step 1: Enter the formula in cell D4

`=IF(LEN(B4)=LEN(C4), "Same", "Not Same")`

Formula Explanation

First, we need to know the basic concepts of the LEN function.

LEN (text)

This function is used to count the character of any text or string. When we pass any text in this function then it will return the number of characters.

LEN(B4) this part first counts the character of each cell from the first column and LEN(C4) for the second one.

If the length is the same then it will print the “Same” and if not then “Not Same”. Step 2: Copy down the formula up to D12 ### 5. Compare Two Cells Text by Occurrences of a Specific Character

Sometimes we may need to compare cells where it will contain the specific characters. In this part, we will see how to compare two cells by Occurrences 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 id. We want to make sure that each row contains an equal number of shipped and received items with that specific ID. Step 1: Enter the formula in cell E4

`=IF(LEN(C4)-LEN(SUBSTITUTE(C4, \$B4,""))=LEN(D4)-LEN(SUBSTITUTE(D4, \$B4,"")), "Same", "Not Same")`

Formula Explanation

Here additionally we have used the SUBSTITUTE function. Let’s see the fundamentals of this function.

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.

For more details, you can visit this Link

SUBSTITUTE(B2, character_to_count,””) using this part we are replacing the unique identifier with nothing using the SUBSTITUTE function.

Then using LEN(C4)-LEN(SUBSTITUTE(C4, \$B4,””)) and LEN(D4)-LEN(SUBSTITUTE(D4, \$B4,””)) 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. Step 2: Copy down the formula up to E9 ### 6. Compare Two Cells Text and Highlight the Matches

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.

Step 1: Select the entire dataset. Step 2: Go to Conditional Formatting. You will find it under the Home tab.

Home > Conditional Formatting Step 3: Select New Rule Step 4: Select the marked option Step 5: Enter the below formula in the mentioned box

`=\$B3=\$C3`

Or you can just select the two columns of the dataset. Step 6:  Click on the Format option Step 7: Then follow the process

1. Go to the Fill tab.
2. Select any color.
3. Then press Ok. Step 8: Click on the Ok button Step 9: See the matched data is highlighted ### 7. Compare Two Cells Text Partially

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.

`RIGHT(text,[num_chars])`

Text -> This is required. The text string holding the characters we require to extract.

Num_chars -> This is optional. Defines the number of characters we require RIGHT to extract.

Let’s consider this data table and we will find if the last 6 characters are matched of the two cells. Step 1: Enter the formula in cell D4 and copy down the formula up to D12

`=RIGHT(B4,5)=RIGHT(C4,5)` [Note: You can try with other functions like LEFT, TRIM for different purposes.]

### 8. Find Matches in any Two Cells in the Same 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. Step 1: Enter the formula in cell E4 and copy down the formula up to E12

`=IF(OR(B4=C4, C4=D4, B4=D4), "Yes", "No")`

Formula Explanation

Here additionally we have used the OR function. Let’s see the fundamentals 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(B4=C4, C4=D4, B4=D4) 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. ### 9. Find the Unique and Matched by Comparing Two cells Text

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 cells match. If at least two cells match then it will be considered as Match otherwise Unique.

Step 1: Enter the formula in cell E4 and copy down the formula up to E12

`=IF(COUNTIF(C4:D4,B4)+(C4=D4)=0,"Unique","Match")`

Formula Explanation

Here the COUNTIF function is used additionally.

COUNTIF (range, criteria)

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. For more details visit this page Link

By using COUNTIF(C4:D4, B4)+(C4=D4)=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. ## Conclusion

These are the ways to do a comparison between two cells’ text 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.  