Aside from staring at them closely, how can you compare two cells in Excel? Here are a few functions and formulas that check the contents of two cells, to see if they are the same. We’ll start with a simple check, then move up the formula ladder, for more complex comparisons.

Easy Way to Compare Two Cells

The quickest way to compare two cells is with a formula that uses the equal sign.

• =A2=B2

If the cell contents are the same, the result is TRUE. (Upper and lower case versions of the same letter are treated as equal).

Ignore Extra Spaces

If you just want to compare two cells, but aren’t concerned about leading spaces, trailing spaces, or extra spaces, use the TRIM function to remove them, for one or both of the cells.

• =TRIM(A2)=TRIM(B2)

That can help if you’re trying to match text strings to the values in an imported list, such as this VLOOKUP example.

To remove other characters, such as non-breaking spaces (character 160), use TRIM with SUBSTITUTE.

The CLEAN function will remove some non-printing characters (characters 0 to 31, 129, 141, 143, 144, and 157).

Compare Two Cells Exactly

If you need to compare two cells for contents and upper/lower case, use the EXACT function. As its name indicate, that function can check for an exact match between text strings, including upper and lower case. It doesn’t test the formatting though, so it won’t detect if one cell has some or all of the characters in bold, and the other cell doesn’t.

• =EXACT(A2,B2)

See more EXACT function examples in my 30 Excel Functions series.

Partially Compare Two Cells

Sometimes you don’t need a full comparison of two cells – you just need to check the first few characters, or a 3-digit code at the end of a string.

To compare characters at the beginning of the cells, use the LEFT function. For example, check the first 3 characters:

• =LEFT(A2,3)=LEFT(B2,3)

To compare characters at the end of the cells, use the RIGHT function. For example, check the last 3 characters:

• =RIGHT(A2,3)=RIGHT(B2,3)

You can combine LEFT or RIGHT with TRIM, if you’re not concerned about the space characters:

• =RIGHT(TRIM(A2),3)=RIGHT(TRIM(B2),3)

And combine LEFT or RIGHT with EXACT, to check if upper/lower case match too. This formula will ignore extra spaces, but checks the case:

• =EXACT(RIGHT(TRIM(A2),3),RIGHT(TRIM(B2),3))

How Much Do Cells Match?

Finally, here’s a formula from UniMord, who needs to know how much of a match there is between two cells. Are the first 5 characters the same? The first 10? What percent of the string in A2, starting from the left, is matched in cell B2?

Here’s a sample list, where the addresses in column A and B and being compared.

Get the Text Length

The first step in calculating the percent that the cells match is to find the length of the address in column A. This formula is in cell C2:

• =LEN(A2)

Get the Match Length

The formula in column D is doing the hard work. It finds how many characters, starting from the left in each cell, are a match. Lower and upper case are not compared.

• =SUMPRODUCT(
  –(LEFT(A3,
  ROW(INDIRECT(“A1:A” & C3)))
  =LEFT(B3,
  ROW(INDIRECT(“A1:A” &C3)))))

How the Match Len Formula Works

The INDIRECT function creates a reference to a range of cells, starting from cell A1. The range ends in column A, in the row that matches the length calculated in column C. So, in row 2, that range is A1:A9.

The ROW function returns the row for each of the rows in that range. That’s why we use ROW/INDIRECT, instead of just referring to the length in cell C2.

In this screen shot, I’ve used the F9 key to calculate that part of the formula, and you can see the row numbers.

Then, the LEFT functions return the characters that are 1, 2, 3…characters to the left in each cell. In this screen shot, I’ve calculated both of the LEFT functions, and you can see that there is a match for lengths 1 through 9.

However, if I do the same thing in row 5, only the first character is a match. After that, the characters are different in the two cells.

The equal sign compares the values for characters 1 through 5 in this example, and returns TRUE if they match, and FALSE if they do not match.

The double minus sign converts each TRUE to a 1, and each FALSE to a zero.

Finally, the SUMPRODUCT function adds up those numbers, to give the number of characters, from the left, that match. In row 5, that total is 1

Get the Percent Match

Once the length and match length have been calculated, it’s easy to find the percent matched. This formula is in cell E2, to compare the lengths:

• =D2/C2

There is a 100% match in row 2, and only a 20% match, starting from the left, in row 5.

Thanks, UniMord, for sharing your formula to compare two cells, character by character.

More Ways to Compare Two Cells

Here are a few more articles that show examples of how to compare two cells – either the full content, or partial content.

• Use INDEX, MATCH and COUNTIF to find codes within text strings. There are other formulas in the comments too, so check those out.
• Compare formulas on different sheets, with the FORMULATEXT and INDIRECT functions. Those functions are volatile though, so they’d slow down the workbook if you use too many of them.
• Be careful when using the Remove Duplicates feature in Excel – it treats real numbers and text numbers as the same value

__________________________

___________________

The post How to Compare Two Cells in Excel appeared first on Contextures Blog.

Original source: http://blog.contextures.com/archives/2018/04/12/how-to-compare-two-cells-in-excel/