Forum Discussion
[12] number in excel
Have a custom number format with [#] format and can't figure out a simple autosum on the rows...
Number in excel can be hundreds of lines, one number per cell;
[8]
[12]
[25]
[4]
But can't figure out how to autosum.. Help.
2 Replies
If the numbers have been entered as 8, 12, etc., and the custom number format \[#\] has been applied, AutoSum should work the normal way.
But if you entered [8], [12], etc., Excel treats the values as text.
To sum the numbers between the square brackets in for example B1:B4, use
=SUM(--SUBSTITUTE(SUBSTITUTE(B1:B4, "[", ""), "]", ""))
or
=SUM(--TEXTBEFORE(TEXTAFTER(B1:B4, "["), "]"))
I could not figure out formatting as a number and then using autosum for total, so went with find and replace to get rid of brackets [ ]...with only numbers left autosum worked fine after that.
