Convert variable length txt field to Z x 1 digit numbers

[Yanta]

Where Z=length of string.

Z can be 0 to 8. Each byte of the string will be a number between 0 and 9.

I couldn't use a numeric field because 00100 becomes 100 and 00000001 becomes 1. The text string must have 1 byte for each time slot.

I referee basketball. There are between 0 and 8 games in a day. Each game is a 'time slot'. I want to count the number of times for each time slot that I call specific events, such as technical, unsportsmanlike or disqualifying fouls (each of these will be separate columns).

I then total those events in a different sheet, which is then input for a graph.

So value(mid($A5,1,1))+value(mid($A5,2,1))+value(mid($A5,3,1)) works fine if the string is 3 bytes, but if it is 0 - 2 bytes I end up with a #Value result.

Data rows start at 5. The source string is in AY. Technical in AZ, Unsportsmanlike in BA and Disqualifying in BB.

I want to Total the number of individual digits for each byte of the string. Can this be done with a single formula?

thank you.

 

[Phuoc]

Try this:

=SUMPRODUCT(--MID(A5,ROW(INDIRECT("1:"&LEN(A5))),1))

or ?

=LEN(A5)-LEN(SUBSTITUTE(A5,"1",""))

 

[Yanta]

Thanks for that. I don't think I would have figured that out. The -- threw me.