Adding zero's to the beginning of a number

[kukarooza]

I'm trying to enter a number into a cell and because the number I nee
to add starts with zero's, Excel automatically changes the number an
removes the zero's. For example, I'm trying to enter the number 00709
and after entering it and going to another cell, the number i
automatically changed to 7099. Is it posisble to keep the cell showin
the numbers like I need them? If so, how do I do it? Thanks

 

[Andrew]

Hi kukarooza,

Simplest way is to add an apostrophe before the number, eg '007099.

If you are entering a lot of numbers, select all the cells (rows o
columns if applicable) and right-click. You'll see Format Cells towar
the bottom. Select the Number tab, then Text and push OK.

One problem with both these solutions is that the numbers will becom
text (you can no longer add, subtract, multiply, divide, count etc) s
if you are always using the same number of zeroes before the rest o
the number, select the Custom from the Number tab in The Format Cell
dialog box and enter "00"0 (or "00"#,##0 if you want a comma fo
thousands, millions etc)

There are other solutions. These are some of the fastest

 

[Bob Flanagan]

Begin the entry with a single quote: '07099

Bob Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Guest]

Bob's suggestion will work, and as far as I know arithmetic will still work, should you need to do any. However it does make the number a "string" rather than a number. This can cause trouble in things like VLookup if you try to match it to an identical number. If these numbers really are strings that just happen to be numbers then Bob's answer is exactly what you need.

If you should, however, need them to retain their character as numbers, you can change the number formatting. This will only work if all of the numbers are of the same length. If they are all 6 characters long you can put a custom format in that looks like: 000000 This will force Excel to display 6 characters with leading zeros when necessary.

Art

Begin the entry with a single quote: '07099

Bob Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Pierre]

if you need to put always, let's say two 0 before your number you ca
change the type (from the format cell) and instead of "general" selec
"custom" and put "0"#,##0.00_) : it will add two zero before and tw
digits

But it works only if you want the same number of 0 before you
numbers...;

 

[Jason Mathews]

The best way to format and keep it as a number is to
highlight the cells you want, click format, cells and
choose custom. Under the word "Type
" , just enter as many zeroes as you want for digits, if
you enter 00000 and click enter, every number will be 5
digits long, using leading zeroes if you type in less than
5 digits.