How do I force a leading zero character eg 07817
 

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

How do I force a leading zero character eg 07817
 

Just enter the numbers preceeded by an apostrophe (single quote)
--
Gary''s Student


"m800afc" wrote:

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

How do I force a leading zero character eg 07817
 

"m800afc" wrote in message
...
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.
--
David Biddulph

How do I force a leading zero character eg 07817
 

I had a similar issue with data containing leading zeroes. Excel would
promptly clip them off making the use of lookups, etc... useless. Here
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), &a2
concatenates the value of a2 to the end. Replace 5 with whatever fixed
length you need.

Regards and good luck!

Jay


--
jbrackett
------------------------------------------------------------------------
jbrackett's Profile: http://www.excelforum.com/member.php...o&userid=32577
View this thread: http://www.excelforum.com/showthread...hreadid=525226