Transform text to array to be able to compare them

[GFC]

For a Multipe Choice Quizz, with 4 possible answers for each questins ABC or D (only one correct), I am looking for a way to compare the 2 strings so as to be able to multiply them by the number of point per questions. Note also that I can get also a 0 (zero) if question Not answered, and "X" if several answers chosen.

So for 5 questions I get a string with 5 letters e.g. "ABXC0"
I look for a function to compare it to the correct answers e.g. "ABCDD"
I need the result in an array so here "11000", so as to be able to mutliply each correct answers by the number of points e.g. "22233"

 

[Somendra Misra]

Hi GFC,

Try below formula:

A1: ABXC0 B1:ABCDD C1:22233
Put formula in A2

=SUMPRODUCT(IF(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)=MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),1,0)*MID(C1,ROW(INDIRECT("1:"&LEN(C1))),1))

Enter with Ctrl+Shift+Enter.

Regards,

 

[Debraj]

If Numbe of Question (5) is fixed.. you can exclude the Indirect function..

=SUM(IF(MID(B1,ROW(1:5),1)=MID(A1,ROW(1:5),1),MID(C1,ROW(1:5),1)+0,0))

 

[GFC]

Thanks so much. Exactly what I was looking for to get rid of all the helper columns used to isolate each letter of the string.