Hi Toppers and David,

thanks for your support!

I was in the middle of posting my reply when I realised my (original)
UDF would work in the same way as you have now reported that David's
formula does - I added the:

Else
Exit Function
End If

at the end of the For .. Next loop to prevent this, just before
posting.

Let's hope the OP tries it for himself.

Pete

Toppers wrote:
I had the same problem with formula but Pete's function works fine.

"Pete_UK" wrote:

Here's a User-Defined function which will do the job:

Function test_string(first As String, second As String) As Integer
Dim first_len As Integer
Dim second_len As Integer
Dim i As Integer
test_string = 0
first_len = Len(first)
second_len = Len(second)
If (first_len = 0 Or second_len = 0) Then test_string = -1: Exit
Function
For i = 1 To Application.WorksheetFunction.Min(first_len, second_len)
If Mid(first, i, 1) = Mid(second, i, 1) Then
test_string = i
Else
Exit Function
End If
Next i
End Function

Just copy/paste it into a normal module.

Use it like the following:

=test_string(A1,B1)

where you have strings to be tested in A1 and B1. It will return the
number of consecutive matching characters as an integer, or -1 if one
of the cells is empty.

Hope this helps.

Pete

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