How can I tell the differences between two strings in Excel?

Question

I created an assessment that applicants fill out in Excel. I have a key where I copy their answers in and it tells me if their answers match my key. I'd like to add a formula that will also show me the differences between the applicant's cell (B2) and the key's cell (A2), to make it easier to see what they got wrong.

I tried using =SUBSTITUTE(B2,A2,"") but this only gives me differences at the beginning or end of the string. Usually, the difference is in the middle.

For example, my key (cell A2) might say: Cold War | Bay of Pigs | Fidel Castro

And the applicant (cell B2) might say: Cold War | Cuban Missile Crisis | Fidel Castro

I want this formula to return: "Cuban Missile Crisis"

Solution 1

You may try something like this...

Function CompareStrings(keyRng As Range, ansRng As Range) As String
Dim arr() As String
Dim i As Long
arr() = Split(ansRng.Value, "|")
For i = 0 To UBound(arr)
    If InStr(keyRng.Value, arr(i)) = 0 Then
        CompareStrings = arr(i)
        Exit Function
    End If
Next i
End Function

Then you can use this UDF like below...

=CompareStrings(A2,B2)

If you want to compare them in the reverse order also and return the not matched string part from any of them, try this...

Function CompareStrings(ByVal keyRng As Range, ByVal ansRng As Range) As String
Dim arr() As String
Dim i As Long
Dim found As Boolean
arr() = Split(ansRng.Value, "|")
For i = 0 To UBound(arr)
    If InStr(keyRng.Value, Trim(arr(i))) = 0 Then
        found = True
        CompareStrings = arr(i)
        Exit Function
    End If
Next i

If Not found Then
    arr() = Split(keyRng.Value, "|")
    For i = 0 To UBound(arr)
        If InStr(ansRng.Value, Trim(arr(i))) = 0 Then
            CompareStrings = arr(i)
            Exit Function
        End If
    Next i
End If
End Function

Use this as before like below...

=CompareStrings(A2,B2)

So the function will first compare all the string parts of B2 with A2 and if it finds any mismatch, it will return that part of string and if it doesn't find any mismatch, it will then compare all the parts of string in A2 with B2 and will return any mismatch part of string. So it will compare both ways.

Solution 2

The function above displays only the first difference. Here is an update that displays all differences between two strings.enter image description here

Function CompareStrings(ByVal keyRng As Range, ByVal ansRng As Range) As String
Dim arr() As String
Dim i As Long
arr() = Split(ansRng.Value, " ")
CompareStrings = "+["
For i = 0 To UBound(arr)
    If InStr(keyRng.Value, Trim(arr(i))) = 0 Then
        CompareStrings = CompareStrings & " " & arr(i)
    End If
Next i
CompareStrings = CompareStrings & " ] -["

arr() = Split(keyRng.Value, " ")
For i = 0 To UBound(arr)
    If InStr(ansRng.Value, Trim(arr(i))) = 0 Then
        CompareStrings = CompareStrings & " " & arr(i)
    End If
Next i
CompareStrings = CompareStrings & " ]"
End Function