Excel formula to compare how similar text is two cells is

Excel formula to compare how similar text is two cells is

September 28, 2019

Speeding Lunatic posted the below VBA code here, which you can use to check how similar text in one cell is to text in another cell.   Put the code in a new module for your workbook and then enter the following formula:

 

=(MAX(LEN($A$1), LEN(A2)) - levenshtein($A$1,A2))/MAX(LEN($A$1), LEN(A2))

 

A1 is your source cell and A2 is the cell you are checking against it.  Naturally you can pull down the formula in order to check how similar subsequent entries are.   This formula is particularly good for finding where variations of a person's name appear in a column.

 

 

 

 

 

 

Function levenshtein(a As String, b As String) As Integer
 
    Dim i As Integer
    Dim j As Integer
    Dim cost As Integer
    Dim d() As Integer
    Dim min1 As Integer
    Dim min2 As Integer
    Dim min3 As Integer
 
    If Len(a) = 0 Then
        levenshtein = Len(b)
        Exit Function
    End If
 
    If Len(b) = 0 Then
        levenshtein = Len(a)
        Exit Function
    End If
 
    ReDim d(Len(a), Len(b))
 
    For i = 0 To Len(a)
        d(i, 0) = i
    Next
 
    For j = 0 To Len(b)
        d(0, j) = j
    Next
 
    For i = 1 To Len(a)
        For j = 1 To Len(b)
            If Mid(a, i, 1) = Mid(b, j, 1) Then
                cost = 0
            Else
                cost = 1
            End If
            min1 = (d(i - 1, j) + 1)
            min2 = (d(i, j - 1) + 1)
            min3 = (d(i - 1, j - 1) + cost)
            d(i, j) = Application.WorksheetFunction.Min(min1, min2, min3)
        Next
    Next
 
    levenshtein = d(Len(a), Len(b))
 
End Function

Please reload

Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

© 2015 by Sean O'Shea . Proudly created with Wix.com