Formula to calculation text string similarities

[Welsh Mark3]

I've been researching for a formula to give me a score of similarities between cell a2 and b2? There are no exact matches in my data, case sensitivity is not an issue.

I see many macros/VBA but I don't have any experience in using these.

Is it possible to create the 'Levenshtein distance' with a formula?

 

[Welsh Mark3]

Similar Company Names

This is the second post, potentially with better wording.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]ABC 123[/TD]
[TD]ABC 12[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am looking for a formula to using in column C that tells how similar the text in A2 is compared to b2? Really struggling with this so any help is appreciated

 

[shknbk2]

Re: Similar Company Names

I found the code below here. I also know that others have posted here with simpler ways to add vba code to your workbook, but I couldn't find it in a short search. Instead, follow the first 3 instructions here. When you get to step 4, simply copy and paste the code below. Then, go back to your spreadsheet, and in C1, type "=Levenshtein(A1,B1)" [no quotes] and hit Enter. Your answer should be 1.

Code to copy:

Public Function Levenshtein(s1 As String, s2 As String)

    Dim i As Integer
    Dim j As Integer
    Dim l1 As Integer
    Dim l2 As Integer
    Dim d() As Integer
    Dim min1 As Integer
    Dim min2 As Integer
    
    l1 = Len(s1)
    l2 = Len(s2)
    ReDim d(l1, l2)
    For i = 0 To l1
        d(i, 0) = i
    Next
    For j = 0 To l2
        d(0, j) = j
    Next
    For i = 1 To l1
        For j = 1 To l2
            If Mid(s1, i, 1) = Mid(s2, j, 1) Then
                d(i, j) = d(i - 1, j - 1)
            Else
                min1 = d(i - 1, j) + 1
                min2 = d(i, j - 1) + 1
                If min2 < min1 Then
                    min1 = min2
                End If
                min2 = d(i - 1, j - 1) + 1
                If min2 < min1 Then
                    min1 = min2
                End If
                d(i, j) = min1
            End If
        Next
    Next
    Levenshtein = d(l1, l2)
End Function

 

[Eric W]

Re: Similar Company Names

You can't get the Levenshtein distance with a native Excel formula, but you can do it with a User-Defined Function (UDF). I wrote the code and an explanation of how to install it here:

https://www.mrexcel.com/forum/excel...d-most-similar-word-word-using-excel-vba.html

post # 7.

You can use the formula

=LevDist(A2, B2)

in C2 if you just want the distance, or

=(Len(A2)-LevDist(A2, B2))/Len(A2)

if you want a percentage.

 

[Welsh Mark3]

Re: Similar Company Names

This givesme a result of #NAME ?

 

[shknbk2]

Re: Similar Company Names

What gives you a #name result? Mine or Eric's. If mine, have you tried Eric's?

 

[Welsh Mark3]

Re: Similar Company Names

The formulas is both, Could this be an issue with the version off excel?

 

[shknbk2]

Re: Similar Company Names

There shouldn't be anything version-specific. When you hit "=" and start typing "Levenshtein" in C1, does it show up in the dropdown formula window like if you were to type vlookup? If not, the code is probably not set up correctly in the macro module.