How to zero fill a number inside of an Excel cell

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).

From stackoverflow

• Format>Cells...>Number>Custom>Type>0000000000

  Lance Roberts : I don't see a 'Special' option under Number in Excel 2003.

  Liudvikas Bukys : Fixed the typo. I meant 'Custom', not 'Special'. Though in my copy of Excel 2003, both are present.

• 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" )
  

  Liudvikas Bukys : right("0000000000"&number,10) works poorly for negative numbers.

• =TEXT(A1,"0000000000")

  Mark Pattison : The disadvantage of this method is that the number itself can no longer be used in other formulae.

  Tmdean : Sure it can, just use VALUE(A1) instead of A1 in the other formulas.

  Mark Pattison : 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.

  mavnn : 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.

  Tmdean : 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 : Obviously if you work with spreadsheets only touched by yourself you can throw caution to the wind.

• Not a programming question, really:

  1. Select cells you want formatted in this way.
  2. Right click and select "Format Cells..."
  3. Select the "Number" Tag, and scroll down to "Custom" in the category list.
  4. Type "0000000000" into the Type field.