How compare two strings and count the number of matched characters?-CodePudding

I want to compare two strings in different cells (in order), and the return value is the number of matched characters.

The characters are limited to: A, B, C, D and E. They are alternatives of a test.

For example:

cell A1: DCBCCBD (the candidate's answers)

cell B1: BABCDBD (the correct answers)

He just asked the third, fourth and sixth question correct.

Then, I need to return in cell C1: 3 (the number of correct answers).

How can I do in MS Excel?

CodePudding user response：

This should do it. I think you are shortchanging your candidate in that he or she got 4 correct.

=SUM(FILTER(--(MID(A1,ROW(A:A),1)=MID(B1,ROW(A:A),1)),ROW(A:A)<=LEN(A1)))

Here's a VBA custom function as mentioned in comments.

Function compareTexts(oneText As String, twoText As String) As Long
Dim i As Long

    For i = 1 To WorksheetFunction.Min(Len(oneText), Len(twoText))
        If Mid(oneText, i, 1) = Mid(twoText, i, 1) Then
            compareTexts = compareTexts   1
        End If
    Next i

End Function

CodePudding user response：

Try MID() with SEQUENCE() then sum true values.

=SUM(--(MID(A1,SEQUENCE(LEN(A1)),1)=MID(B1,SEQUENCE(LEN(B1)),1)))

-- suppress Boolean output to equivalent number values to sum.