leading zeros

[erenkey]

I need help with a formula that will add leading zeros to a number. I need a formula that will make all of the numbers 9 characters.

Example:
Column A Column B(desired result)
1234 000001234
12345 000012345
123456 000123456
1234567 001234567
12345678 012345678
123456789 123456789

The numbers in column A will be anywhere from 2-9 digits but I need all of them to be 9 digits.

 

[erik.van.geit]

Hi,

If you mean that they should "appear" like that, then have a look at the format of the cells.
cellproperties / tab: number
choose from list: "custom"
write 00000000
(9 zeros)

<TABLE style="WIDTH: 53pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=70 border=0 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" align=right width=70 height=17 x:num="123">000000123</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ccffcc" align=right height=17 x:num="1">000000001</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" align=right height=17 x:num="444444">000444444</TD></TR></TBODY></TABLE>

kind regards,
Erik

 

[erenkey]

Instead of the zeros appearing to be there, I need the zeros to actually be there. I know I need to change the numbers into text but I don't know how to add the leading zeros.

 

[PaddyD]

=text(a1,"000000000")

 

[erenkey]

I figured it was something easy like that. Thanks.