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