Excel formula to compare how similar text is two cells is

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