How do I force a leading zero character eg 07817

[Guest]

I am entring in order codes on a stock order sheet. The codes have 5 digits.
Some of them begin with 0 (zero). How do I force the cell to report the
correct number, ie 07817, and not 7817 as it is doing at the moment?

Thanks

Tony

 

[Guest]

Just enter the numbers preceeded by an apostrophe (single quote)

 

[David Biddulph]

I am entring in order codes on a stock order sheet. The codes have 5
digits.
Some of them begin with 0 (zero). How do I force the cell to report the
correct number, ie 07817, and not 7817 as it is doing at the moment?

Format the cell as text before you enter the number.

 

[jbrackett]

I had a similar issue with data containing leading zeroes. Excel woul
promptly clip them off making the use of lookups, etc... useless. Her
is what I did:
(Assuming you need a fixed length of 5 char)

A B C
1 NUM DESC FORMULA RESULT
2 1 apple =REPT("0",5-LEN(a2))&A2 = 00001
3 22 orange =REPT("0",5-LEN(a3))&A3 = 00022
4 304 pear =REPT("0",5-LEN(a4))&A4 = 00304

What it does:
=REPT("s",x) Repeats "s" (or whatever string), x times)
=LEN(a2) Returns the char count of a2

The combined formula repeats "0" for (5 - length of a2), &a
concatenates the value of a2 to the end. Replace 5 with whatever fixe
length you need.

Regards and good luck!

Ja