How to compare the similarity of 2 cells?

Hi guys, I want to compare the similarity of the 4-digit primary SIC code of an acquirer, and the 4-digit primary SIC code of a target. It's for my Master's thesis.

If the first digit of each code doesn't match: similarity = 0
If the first digit of each code matches: similarity = 1
If the two first digits of each match: similarity = 2
If the three first digits of each matches: similarity = 3
If the all the digits of each code matches: similarity = 4

What is the right formula?

Example:

Acquirer SIC	Target SIC	Similarity
1896	1896	4
1963	1900	2
6952	6327	1
5369	5369	4
6321	6312	2

try this formula...

=IF(LEFT(A1,4)=LEFT(B1,4),4,IF(LEFT(A1,3)=LEFT(B1,3),3,IF(LEFT(A1,2)=LEFT(B1,2),2,1)))

Hi glux,

FDibbins was short a zero so his formula was close but not exact. See the attached with another way to get the answer using:

Please Login or Register  to view this content.

It looks like Pete found the error also and improved it. Because you say the problem has only digits my formula does the same thing.