Leading zeroes formula

[Steve]

Hi everyone. In column B I have a list of numbers. Each number
varies in length, but no number is longer than 6 cbaracters. I need a
formula that will make each number rxactly 6 characters by attaching
leading zeroes to the FRONT if the number is less than 6 characters,
or do nothing if the number is already 6 characters. The "number" can
be converted to text.

Thanks!

 

[Mike H]

Hi,

Select the cell(s) and then

Format|cells - Custom

enter a custom format of 000000 That's 6 zeroes.

Mike

 

[Jarek Kujawa]

=IF(LEN(A1)<6,REPT("0",6-LEN(A1))&A1,IF(LEN(A1)=6,A1,""))

HIH

 

[Jarek Kujawa]

of course I meant

=IF(LEN(B1)<6,REPT("0",6-LEN(B1))&B1,IF(LEN(B1)=6,B1,""))

HIH