There are 2 ways and again I'm not sure this is what you want.
Method 1
On cell C5, they the following formula.
=+IF(ABS(B5<10),"0000000"&B5,IF(ABS(B5)<100,"00000 0"&B5,IF(ABS(B5)<1000,"00000"&B5,IF(ABS(B5)<10000, "0000"&B5,IF(ABS(B5)<100000,"000"&B5,IF(ABS(B5)<10 00000,"00"&B5,IF(ABS(B5)<10000000,"0"&B5,B5)))))))

ON Cell B5 is where you enter the number. On Cell C5, it displays the
number as text filling in the required number of zeros.

Method 2
Use a vlookup as follows
Create a table array in say, F1 to G8. Enter the following numbers on
each cell.
F1=0 G1='0000000
F2=10 G2='000000
F3=100 G3='00000
F4=1000 G4='0000
F5=10000 G5='000
F6=100000 G6='00
F7=1000000 G7='0
F8=10000000 G8=(blank)

the zeroes in column G are entered as text.
In Cell C1 Enter the following formula
=+VLOOKUP(B1,$F$1:$G$8,2)&B1

Enter your values in B1. Cell C1 displays the value with leading 0's
as text.
The $, I assume you know, is so that you can copy that formula down
without changing the table it references.
The concept is it looks up the number you typed and compares it to the
table, returns the correct number of leading 0's + your number.

I hope this helps.


--
DennisSunga
------------------------------------------------------------------------
DennisSunga's Profile: http://www.excelforum.com/member.php...o&userid=27514
View this thread: http://www.excelforum.com/showthread...hreadid=471900