Lots of 00000000 before number Autosum do not add up

74222464

New Member
Joined
Mar 19, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
 
paste formula without curly brackets
=SUM(--TRIM(CLEAN(SUBSTITUTE(E3:E26,CHAR(160),""))))
it will be added automatically after Ctrl+Shift+Enter
adjust references of course

cse.png
 
Last edited:
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
50.25 =SUM(--TRIM(CLEAN(SUBSTITUTE(E3:E26,CHAR(160),""))))
142.80
265.20
214.20
43.07
7.79
0.72
65.39
17.61
26.34
35.37
14.70
 

Attachments

  • Still doing the same.png
    Still doing the same.png
    110.7 KB · Views: 4
Upvote 0
as I see your numbers are numbers without zeroes in front of them so maybe select them and change format to General or Number ?

problem is because Microsoft know better what user want. If the cell is in text format so the next cell to the right copy format (text) and you see formula not result of formula
Idiotic thing but what we can do? Live with it :cool:
 
Upvote 0
as I see your numbers are numbers without zeroes in front of them so maybe select them and change format to General or Number ?

problem is because Microsoft know better what user want. If the cell is in text format so the next cell to the right copy format (text) and you see formula not result of formula
Idiotic thing but what we can do? Live with it :cool:
Thanks, I eventually used revert text to colums and delimit the space and that does the job, a pity it cannot be a permanent thing but as you say I have to live with it. Thanks for all you help
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top