Lots of 00000000 before number Autosum do not add up

[74222464]

Good Day I have tried many ways to use autosum from all over. A company use 10 zero's infront of the number and when I want to use autosum it give a 0 as answer. Any formulas that I can use, I used a Macro I found on Google and also this one =SUBSTITUTE(H1,CHAR(160),"")+0 but even that did not do the job for long. I really need something to use on a permanent basis to add up figures please. Pete

 

[sandy666]

is that what you want?

Book1
	B
2	0000000000
3	0000000022
4	0000011000
5	0000000050
6	11072
Sheet2

Cell Formulas
Range	Formula
B6	B6	=SUM(--B2:B5)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[sykes]

Hi Pete, and welcome to Mr. Excel!
I suggest posting some sample data - perhaps 10 of the cells you're trying to sum.

This is a very handy tool for uploading a copy of the selected parts of your spreadsheet - which allows members to not only have a butcher's at how it looks, but also download it quickly at their end - allowing much quicker resolutions (normally!).

Oh, disregard - @sandy666 has already been here...
At least it's a good demonstration of use of the tool to which I referred...

 

[74222464]

is that what you want?

142.80

265.20

214.20

Hi Pete, and welcome to Mr. Excel!
I suggest posting some sample data - perhaps 10 of the cells you're trying to sum.

This is a very handy tool for uploading a copy of the selected parts of your spreadsheet - which allows members to not only have a butcher's at how it looks, but also download it quickly at their end - allowing much quicker resolutions (normally!).

Oh, disregard - @sandy666 has already been here...
At least it's a good demonstration of use of the tool to which I referred...

Thanks I installed will now see if it work:

142.80

265.20

214.20

 

[74222464]

is that what you want?

Book1
	B
2	0000000000
3	0000000022
4	0000011000
5	0000000050
6	11072
Sheet2

Cell Formulas
Range	Formula
B6	B6	=SUM(--B2:B5)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Hi Thanks i will upload another example from what I experienced:

97.44
3.00
28.61
24.69
25.16
6.25
6.25
8.75
0.70
0.76
6.25
3.75
6.25
0.63
2.93
0.15
2.50
0.25
12.50
1.00
52.83
99.71
162.13
423.87

I hope this is the correct format

 

[74222464]

I also think that it is blank spaces of nothing in front of the numbers

 

[sandy666]

I hope this is the correct format

you said you've multizeroes in front of each number but I don't see that
you example works with standard AUTOSUM

Book1
	E
3	97.44
4	3
5	28.61
6	24.69
7	25.16
8	6.25
9	6.25
10	8.75
11	0.7
12	0.76
13	6.25
14	3.75
15	6.25
16	0.63
17	2.93
18	0.15
19	2.5
20	0.25
21	12.5
22	1
23	52.83
24	99.71
25	162.13
26	423.87
27	976.36
Sheet2

Cell Formulas
Range	Formula
E27	E27	=SUM(E3:E26)

or, if you think there are spaces you can try with:

Book1
	E
3	97.44
4	3
5	28.61
6	24.69
7	25.16
8	6.25
9	6.25
10	8.75
11	0.7
12	0.76
13	6.25
14	3.75
15	6.25
16	0.63
17	2.93
18	0.15
19	2.5
20	0.25
21	12.5
22	1
23	52.83
24	99.71
25	162.13
26	423.87
27	976.36
Sheet2

Cell Formulas
Range	Formula
E27	E27	=SUM(--TRIM(E3:E26))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[sandy666]

or even you can try: =SUM(--TRIM(SUBSTITUTE(E3:E26,CHAR(160),""))) with Control+Shift+Enter
or if there are any non-printable characters you can add CLEAN() function : =SUM(--TRIM(CLEAN(SUBSTITUTE(E3:E26,CHAR(160),"")))) with Control+Shift+Enter

 

[74222464]

I will try and see thanks. Have to run to a meeting

 

[74222464]

Hi sorry for only coming back now. When I try to use one of the formulas you suggest example (=SUM(--TRIM(CLEAN(SUBSTITUTE(E3:E26,CHAR(160),"")))) it give me this error I have uploaded. Any new suggestions. Is my settings within excel wrong?