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.

**Table of Contents**hide

## Download the Practice Workbook

**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

- Go to the
**Fill**tab. - Select any color.
- 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.

For more details visit this page Link

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

**Read More:** Excel Count Matches in Two Columns (4 Easy Ways)

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

## Further Readings

- Excel Compare Text in Two Columns (7 Fruitful Ways)
- Match Two Columns in Excel and Return a Third (3 Ways)
- Excel formula to compare two columns and return a value (5 examples)
- VLOOKUP Formula to Compare Two Columns in Different Sheets!
- How to Compare Two Columns or Lists in Excel
- How to Compare Two Columns in Excel For Finding Differences