SUM of Character CODE in a variable length string

Hello.


I am trying to SUM all of the character codes in a given cells string. I have it working, but it is only working for a fixed length string right now, here is what I am using (sums up to a 10 character long string in E6):

Please Login or Register  to view this content.

Psuedoformula for what I need is:

Please Login or Register  to view this content.

But that doesn't work, nor does

Please Login or Register  to view this content.

I'm missing a rule somewhere, still learning how to use CSE's!

=sum(code(mid(e6,row(a1:index(a:a,len(e6))),1)))

The following will work for you

=SUM(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))

Perfect! Thanks

I am wondering, why didn't my version work? ROW(1:LEN() didn't work, but ROW(A1:INDEX() worked? Does the array in the ROW formula have to be either both static values OR both Formula & References?


Edit: TheCman81 answered the above question for me, thanks both! My excel understanding grows...

Another option (although I prefer Andy's or CMan's LOL)

=SUM(CODE(MID(E6&REPT(2,25-LEN(E6)),ROW(1:25),1)))-50*(25-LEN(E6))

where 25 is greater than the maximum number of characters you'd expect in E6

I guess excel does not know how to parse the range definition returned by 1:INDEX(A:A,LEN(A6))
Adding column letter or using complete row clarifies the reference.

=SUM(CODE(MID(E6,ROW(1:1:INDEX(A:A,LEN(E6))),1)))

Thanks all

Here's a completely useless adaptation of TheCman81's formula I made in thanks,

=CHAR(ROUNDDOWN(AVERAGE(SUM(CODE(MID(E6,ROW(INDIRECT("1:"&LEN(E6))),1)))/LEN(E6)),0))

Find the average character of whatever string Is in E6, because Excel!