automated leading zero

[sauk304]

Hi Excel Gurus,

i have question, how to automated leading zero in my excel ? example ;-

1
12
123
1234
12345

i would like the result to be :-

0000000001
0000000012
0000000123
0000001234
0000012345

i don't want to use format cell /custom as the solution, because after i add leading zero i need to perform vlookup to other worksheet.

kindly help ?

 

[Kiwiman]

Howzit

Have a formula column next to it:

=Right("00000000"&len(yourcell),9)

 

[Kiwiman]

Howzit

Or you can nest it in the vlookup itself

=vlookup(right("00000000"&len(yourcell),9),yourtable,n,false)

 

[NBVC]

Not sure if that formula in Post 2 works correctly....

Try:

=TEXT(A1,REPT(0,10))

where A1 is first cell to convert, then copy down

 

[Kiwiman]

mea culpa. I hadn't come across that rept function either - very nice.