How do you zero fill a number to 10 positions inside an excel spreadsheet?
i.e. If cell A1 has 1234 how can cell A2 display 0000001234 (10 postions).
I don't see a 'Special' option under Number in Excel 2003.
Lance Roberts
2009-03-12 15:54:20
Fixed the typo. I meant 'Custom', not 'Special'. Though in my copy of Excel 2003, both are present.
Liudvikas Bukys
2009-03-12 16:22:28
A:
Something like the following.
right( "0000000000" & number, 10 )
Put 10 zeroes on the left, take the right-most 10 positions, whatever they turn out to be.
Also, you have
text( number, "0000000000" )
S.Lott
2009-03-12 15:25:06
Liudvikas Bukys
2009-03-12 16:25:12
The disadvantage of this method is that the number itself can no longer be used in other formulae.
Mark Pattison
2009-03-12 16:11:08
True, but it then means you have to change all cells refering to A1. Much easier to use mavnn's answer below, to be honest.
Mark Pattison
2009-03-17 11:48:32
Depends what you want: I've seen a lot of people get very confused when formatting disguises the actual value in a cell (which is what my solution does, in effect). I actually marked up Alison's solution as ihmo it's more useful in more situations that simply changing the format.
mavnn
2009-03-18 11:35:38
I've just gotten in the habit of always using VALUE when I want a number since it's shockingly common to get a spreadsheet that has a number formatted as text on row 4934 and there's no way to be able to tell without examining every cell your formula applies to.
Tmdean
2009-03-18 21:23:32
Obviously if you work with spreadsheets only touched by yourself you can throw caution to the wind.
Tmdean
2009-03-18 21:24:07
+3
A:
Not a programming question, really:
- Select cells you want formatted in this way.
- Right click and select "Format Cells..."
- Select the "Number" Tag, and scroll down to "Custom" in the category list.
- Type "0000000000" into the Type field.
mavnn
2009-03-12 15:26:10