Leading Zero's in Excel

[Guest]

How do I show Leading Zero's in Excel

 

[Gord Dibben]

Several methods.

Precede the number entry with an apsotrophe '001234

Pre-format the cell as text.

Set up a custom format like 0000000 depending upon the number of digits in the
number.


Gord Dibben MS Excel MVP

 

[Alan]

Either format the cell as Text before you enter the value or enter it with a
leading apostrophe, for example '0001234. The apostrophe will show in the
formula bar but not in the cell.
Regards,
Alan.

 

[Guest]

you can use formatting
for example if you want
2 to appear as
0001

then format the cell as custom 0000

 

[Pete_UK]

And I thought formatting only changed the appearance, not the value
<bg>

I think you meant that 2 would appear as 0002 when formatted that way.

Pete

 

[Guest]

Thanks for the correction
(I can't even blame the spell-checker this time.)

 

[Krl]

OK, this one is little more tricky. I am exporting numbers that are four
digits long, but may have only one two or three actual numbers? The digits
that preceed the actual numbers must be zero (eg- 0001, 0011, 0111). The
program that I export them from has the leading zeros in the fields, but they
don't show up on the spreadsheet and omit any zero that preceeds an actual
number. Depending on how many intagers that are in the number, the number of
leading zeros will be different.

 

[Gord Dibben]

Just try it. You may like it.

Format to Custom 0000

1 will show as 0001
11 will show as 0011
111 will show as 0111
1111 will show as 1111


Gord Dibben MS Excel MVP

 

[debbie]

How do I show Leading Zero's in Excel

Hi,
here is my particular problem: I want to enter isbn's into my excel sheets.
Some start with 0 and some start with 1. If I select the column as 'text'
before entering any numbers, I have no problem. But if I copy and paste a
number from the order site, all of the isbns that start with zero lose the
leading zero upin the paste. Any ideas?

 

[Dave Peterson]

Format the cell as Text.

And paste directly into the formulabar