link to the page
4) Partially Compare Two Cells
Sometimes you don’t need a full comparison of two cells – you just need to check the first few characters at the left, or a 3-digit code at the right end of a string.
The following examples use the LEFT and RIGHT functions to compare partial text in two different cells.
Compare from Left
To compare characters at the beginning of the cells, use the LEFT function. For example, check the first 3 characters:
Compare from Right
To compare characters at the end of the cells, use the RIGHT function. For example, check the last 3 characters:
Ignore Extra Space Characters
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))
5) Find Percent 2 Cells Match
Finally, here’s a formula from UniMord, that checks how much of a match there is between two cells. UniMord created this formula for a project at work, where he needed to compare old and new addresses.
Here’s a sample list, where the addresses in column A and B and being compared.
Percent Match Formulas
With formulas in columns C, D and E, we can calculate a percentage match, by comparing the address in column B to the original address in column A.
Here's what the 3 formulas do, and the formula details are shown in the sections below:
- Column C: Calculate length of text in column A. How many characters are in the cell?
- Column D: Based on that number of characters, how many characters in column B are a match, starting from the left?
- Column E: Compare results from first two formulas, to calculate the percent match
Col C: Get 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:
Col D: Get 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.
Note: There are 2 formulas shown below-- one for Excel 365, and one for earlier versions of Excel
A) For Excel 365, or other versions that have the new Spill Functions, use this formula:
- =SUMPRODUCT(--(LEFT(A2, SEQUENCE(C2))
=LEFT(B2,SEQUENCE(C2))))
B) For earlier versions of Excel, use this formula -- it will work in any version of Excel:
- =SUMPRODUCT( –(LEFT(A2, ROW(INDIRECT(“A1:A” & C2)))
=LEFT(B2, ROW(INDIRECT(“A1:A” &C2)))))
How the Formulas Work
The example in the screen shot below shows a summary of the steps.
- Get the characters from the left of each cell
- Compare the characters, to see if they are equal
- Convert TRUE and FALSE to 1 and 0
- Get a sum of the 1s and 0s
For more details on how these two Match Length formulas work, go to the How Match Len Formula Works section below.
Col E: Get the Percent Match
Once the text length and the match length have been calculated, it just takes a simple formula to find the percent matched. Here is the formula from cell E2, to compare the two lengths:
There is a 100% match in row 2, and only a 20% match, starting from the left, in row 5.
How Match Len Formula Works
Note: You can skip reading this section, unless you want more details on how the Match Length formulas work.
In column D, the Match Len formula compares the 2 cells, to see how many characters match, starting from the left. To get the result, the formula does 4 things:
1. Get Cell Characters
To find out how many characters have an equal match, the formula will use the LEFT function to:
- get characters from the two cells
- start from the left
- start with 1 character, and go to X characters
Usually, we enter a single numbers as the second argument for the LEFT function. However, for this formula, we want the LEFT function to check a list of numbers, from 1 to X.
If we typed the list of numbers in the formula, the numbers would be in an array, like this:
- =LEFT(A2,{1;2;3;4;5;6;7;8;9}) = LEFT(B2,{1;2;3;4;5;6;7;8;9})
Create Array of Numbers with Formula
We don't want to type an array of numbers into the formula though. We want Excel to automatically create a list of numbers, starting with 1, and ending at X. (X is the length of Address01, in this example)
There are two formulas shown below, so use that one that works in your version of Excel:
A) Array of Numbers - Excel 365Use this shorter formula, in Excel 365, or other versions that have the new Spill Functions. In it, the SEQUENCE function creates the list of numbers:
- =SUMPRODUCT(--(LEFT(A2, SEQUENCE(C2))
=LEFT(B2, SEQUENCE(C2))))
For earlier versions of Excel, use this longer formula. It will work in any version of Excel, and uses two functions to create the list of numbers - ROW and INDIRECT:
- =SUMPRODUCT( –(LEFT(A2, ROW(INDIRECT(“A1:A” & C2)))
=LEFT(B2, ROW(INDIRECT(“A1:A” & C2)))))
Here's how the ROW and INDIRECT functions work together, to create the list of numbers:
- First, the INDIRECT function creates a reference to a range of cells -- “A1:A” & C2
- In row 2, that calculates to “A1:A9”
- Next, the ROW function returns an array, with the row number for each cell in that range;
- In row 2, that array is {1;2;3;4;5;6;7;8;9}
In the screen shot below, you can see the list of numbers that the two functions create. To make that list appear, follow these steps:
2) Are Cell Character Strings Equal?
After the LEFT function gets character strings from each cell, the equal sign compares those stings.
- The result from each comparison is TRUE (equal) or FALSE (not equal)
In the screen shot below, there were 5 character strings to compare. The first result is TRUE, and the remaining four results are FALSE.
3) Change TRUE and FALSE to Numbers
Next, the double minus sign, near the start of the formula, converts:
- each TRUE to a 1 (one)
- each FALSE to a 0 (zero)
4) Add the Numbers
Finally, the SUMPRODUCT function adds up those numbers, to calculate the total number of characters, from the left, that match.
In cell D5, in the screen shot below, the total is 1.
- Starting from the left in cells A5 and B5, only 1 character was a match.
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.
__________________________
Download the Workbook
Compare Cells: Download the sample file with Compare Cells examples from this tutorial. The zipped Excel file is in xlsx format, and does not contain macros.
Related Tutorials
Compare 2 Lists
SUMPRODUCT function
INDEX / MATCH Functions
INDIRECT function
EXACT Function Examples
VLOOKUP Troubleshooting
VLOOKUP Examples
Excel Spill Functions