Search code examples
excelexcel-formulamatchworksheet-function

Comparing how many letters match

I'm trying to compare two cells in Excel to retrieve a numeric value, for example:

Trial type   Duration    Letters shown   Distracters    Laterality   Response  Full  
 6           80          XZHTEJ          0              Bilateral    TEHZ

In that line I would like to compare the letters shown with the response, and return a number for how many are correct. I would like it to have the number 4, as there are four letters typed that match the letters shown.

Solution

  • VBA might be more suitable but you have not tagged as such and a formula should work: 

    =IFERROR(FIND(MID(C1,1,1),F1)>0,0)+IFERROR(FIND(MID(C1,2,1),F1)>0,0)+IFERROR(FIND(MID(C1,3,1),F1)>0,0)+IFERROR(FIND(MID(C1,4,1),F1)>0,0)++IFERROR(FIND(MID(C1,5,1),F1)>0,0)+IFERROR(FIND(MID(C1,6,1),F1)>0,0)