On Tue, 5 Sep 2006 07:43:02 -0700, andy62
wrote:

I am wondering if there is any trick for identifying how much of a match two
text strings are? In my case, the two strings would start the same, but then
differ somewhere in the middle or the end. If I could identify the character
position where the difference occurs that would help me - and then maybe
divide that number by the length in characters of the first string to get a
percentage.

TIA

The following **array-entered** formula will return the number of the first
character that does not match.

To **array-enter**, after typing or pasting the formula, hold down
<ctrl<shift while hitting <enter. Excel will place braces {...} around the
formula:

=MATCH(FALSE,LEFT(A1,ROW(INDIRECT("1:"&LEN(A1))))= LEFT(A2,ROW(INDIRECT("1:"&LEN(A2)))),0)

To obtain the "percent" as you describe, merely divide that result by the
length of the first string:

=MATCH(FALSE,LEFT(A1,ROW(INDIRECT("1:"&LEN(A1))))= LEFT(A2,ROW(INDIRECT("1:"&LEN(A2)))),0)/LEN(A1)

The formulas are NOT case sensitive.

For a case-sensitive formula, use:

=MATCH(FALSE,EXACT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A 1)))),
LEFT(A2,ROW(INDIRECT("1:"&LEN(A2))))),0)

The formulas may give an NA result if A1 is shorter than A2.

There are a variety of ways of handling the problem depending on the results
you want:

=MATCH(FALSE,LEFT(A1,ROW(INDIRECT("1:"&MAX(LEN(A1: A2)))))=LEFT(A2,ROW(INDIRECT("1:"&LEN(A2)))),0)

Of course, when you divide that value by the length of the first string, you
will get a value slightly greater than 1, so if you choose to do this, you will
have to decide how you want to compute the percentage match.

Perhaps:

=MATCH(FALSE,LEFT(A1,ROW(INDIRECT("1:"&MAX(LEN(A1: A2)))))
=LEFT(A2,ROW(INDIRECT("1:"&LEN(A2)))),0)/MAX(LEN(A1:A2))

to divide by the longest of A1:A2.


--ron