How to compare the text in two cells and return a value of number of different characters

[madameblue]

Hello,

I am trying to figure out a way to compare the cells in a datasheet and get a value of differences.

I have text I gave to students to type, and I have transcribed the text as they typed, with all errors and space, etc. and I want to compare their input to the original text they were supposed to type, and get a number of errors, so it will compare all characters, and spaces too.

Is there a way to do it? so I don't have to manually input errors?

Thanks for any guidance on this.

M.

 

[joemeshuggah]

Maybe something along these lines...it compares A1 to B1 and puts the number of discrepancies in C1 (it can be made to be dynamic as well, though). Not sure how to get it to disregard additional spaces (e.g. if an additional space was typed it offsets the comparison and makes the number of errors look to be much higher)


Sub Test()
CellLengthOne = Len(Range("A1"))
CellLengthTwo = Len(Range("B1"))
If CellLengthTwo > CellLengthOne Then
CellLengthMain = CellLengthTwo
Else
CellLengthMain = CellLengthOne
End If

x = 0
For i = 1 To CellLengthMain
If Mid(Range("A1").Value, i, 1) <> Mid(Range("B1").Value, i, 1) Then
x = x + 1
End If
Next i
Range("C1").Value = x

End Sub