Excel Compare Two Cells Text (9 Examples)

Copy down the formula up to D12

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.

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.

Compare Two Cells Text Using Formula (Case Insensitive)

Step 1: Enter the formula in cell D4.

=B4=C4

Enter the formula in cell D4

Step 2: Copy down the formula up to D12

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.

Compare two cells using EXACT function

Step 1: Enter the formula in cell D4.

=EXACT(B4, C4)

Enter the formula using EXACT

Step 2: Copy down the formula up to D12

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.

Observesion of Excat function

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.

Exact function with IF

Step 2: Copy down the formula up to D12

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")

Enter formula using IF function

Step 2: Copy down the formula up to D12

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.

Compare Two Cells Text by String Length

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

Enter formula using IF and len function

Step 2: Copy down the formula up to D12

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.

Compare Two Cells Text by Occurrences of a Specific Character

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.

Enter the formula in cell E4

Step 2: Copy down the formula up to E9

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

Select the dataset

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

Home > Conditional Formatting

 Go to conditional Formatting

Step 3: Select New Rule

Select New Rule

Step 4: Select the marked option

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.

Enter formula or select the column

Step 6:  Click on the Format option

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.
     Process of Format Cells

Step 8: Click on the Ok button

Click on Ok button

Step 9: See the matched data is highlighted

See the matched data 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.

Compare two cells text partially

Step 1: Enter the formula in cell D4 and copy down the formula up to D12

=RIGHT(B4,5)=RIGHT(C4,5)

Enter the formula using RIGHT function

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

Find Matches in any Two Cells in the Same Row

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.

Enter the formula cell E4 and copy down it

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.

Enter the formula for getting unique and matched values

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.

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo