This one will work... it's a long one. It will output the position of the
last 2 characters that match. It assumes the "first" string is in A1 and the
"second" is in A2. To finally get the percentage value divide the whole
thing by LEN(A1). Also, make sure to commit it with CTRL+SHIFT+ENTER:

=MAX(ROW(INDIRECT("1:"&MAX(LEN(A1),LEN(A2))))*(MID (A1,ROW(INDIRECT("1:"&MAX(LEN(A1),LEN(A2)))),1)=MI D(A2,ROW(INDIRECT("1:"&MAX(LEN(A1),LEN(A2)))),1)))

--
Regards,
Dave


"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

Hmmm . . . maybe I am doing something wrong, but this formula seems to find
the last 2 characters that match regardless of whether any of the preceeding
ones match. In one of the ones I tested, two text strings both had an "s" in
position 87, although none of the preceeding words or characters matched. I
need to determine the position where every preceeding character matched, but
the remaining ones do not. Sorry if I am doing something wrong, or if my
original explanation was unclear.

"David Billigmeier" wrote:

This one will work... it's a long one. It will output the position of the
last 2 characters that match. It assumes the "first" string is in A1 and the
"second" is in A2. To finally get the percentage value divide the whole
thing by LEN(A1). Also, make sure to commit it with CTRL+SHIFT+ENTER:

=MAX(ROW(INDIRECT("1:"&MAX(LEN(A1),LEN(A2))))*(MID (A1,ROW(INDIRECT("1:"&MAX(LEN(A1),LEN(A2)))),1)=MI D(A2,ROW(INDIRECT("1:"&MAX(LEN(A1),LEN(A2)))),1)))

--
Regards,
Dave


"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

David,
On my testing:

Incorrect match: doesn't pick up incorrect "a" in sentance

This is a very long sentence to test for match
This is a very long sentance to test for match

Correct match: finds missing "s" in sentence

This is a very long entence to test for match
This is a very long sentance to test for match

... and here too...

This is a very long entence to test for match
This is a very long sentence to test for match


and it outputs postion of last character that matches (20 in correct match
above but 46 in incorrect one).

Comparing ...

abcdfg

axcdef

returns an answer of 4 rather than 1.

Over to you!

"David Billigmeier" wrote:

This one will work... it's a long one. It will output the position of the
last 2 characters that match. It assumes the "first" string is in A1 and the
"second" is in A2. To finally get the percentage value divide the whole
thing by LEN(A1). Also, make sure to commit it with CTRL+SHIFT+ENTER:

=MAX(ROW(INDIRECT("1:"&MAX(LEN(A1),LEN(A2))))*(MID (A1,ROW(INDIRECT("1:"&MAX(LEN(A1),LEN(A2)))),1)=MI D(A2,ROW(INDIRECT("1:"&MAX(LEN(A1),LEN(A2)))),1)))

--
Regards,
Dave


"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

Yep you are right, my formula should have included something to stop checking
after it finds an incorrect match. Although my definition of the formula was
correct: "It will output the position of the last 2 characters that match,"
it will only fulfill the subject's request if the 2 strings are complete
opposites after the first non-match. Sorry folks!

You should use Pete's UDF instead.

--
Regards,
Dave


"Toppers" wrote:

David,
On my testing:

Incorrect match: doesn't pick up incorrect "a" in sentance

This is a very long sentence to test for match
This is a very long sentance to test for match

Correct match: finds missing "s" in sentence

This is a very long entence to test for match
This is a very long sentance to test for match

.. and here too...

This is a very long entence to test for match
This is a very long sentence to test for match


and it outputs postion of last character that matches (20 in correct match
above but 46 in incorrect one).

Comparing ...

abcdfg

axcdef

returns an answer of 4 rather than 1.

Over to you!

"David Billigmeier" wrote:

This one will work... it's a long one. It will output the position of the
last 2 characters that match. It assumes the "first" string is in A1 and the
"second" is in A2. To finally get the percentage value divide the whole
thing by LEN(A1). Also, make sure to commit it with CTRL+SHIFT+ENTER:

=MAX(ROW(INDIRECT("1:"&MAX(LEN(A1),LEN(A2))))*(MID (A1,ROW(INDIRECT("1:"&MAX(LEN(A1),LEN(A2)))),1)=MI D(A2,ROW(INDIRECT("1:"&MAX(LEN(A1),LEN(A2)))),1)))

--
Regards,
Dave


"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