Average numbers within a cell

[prudential]

Hi, can someone please help me with this formula. The current formula is in the B column of attached file but its calculating 0s as 5 rather than 10s. Each 0 represents a 10.

example_excelforum2.xlsx

thanks in advance

[shg]

One way:

Row\Col	A	B	C
1	00	10	B1: {=AVERAGE(IFERROR(CODE(MID(SUBSTITUTE(A1, 0, ":"), {1,2,3,4,5,6,7,8,9,10},1)) - 48, FALSE))}
2	024	5.333333
3	211025	3.5
4	111	1
5	1027	5
6	23	2.5
7	45854	5.2
8	128	3.666667
9	60	8
10	854360	6
11	00	10
12	203235	4.166667

[prudential]

shg, that works perfectly. thank you.

[shg]

You're welcome.