adding leading zeros

[jonty]

Hallo,
I have set of number say from A1 to A10
say 23 , 343 , 45345334 , 4353453 etc

I need a formula that will make all these number to be 10 digits be adding zeros to the front of the number
ie 0000000023 , 0000000343 , 0045345334 etc

I think i can do it but doing an if statment ie if len(a1)=1, +"000000000"&a1 , ..... but that seems to be a long way around - does anyone know if there is a quicker easier way of doing this

Thanks a lot
Jonty

[amitshah]

If I understand your question right ......

You can select the column or cells that require the 10 digits.

Right click and select format cells.
Select the Number tab and select custom in category list

insert 0 ten times (if you want 10 digits ) .........

select ok

hope this helps ........

[jonty]

thanks a lot -

the simple things are often the best !!!!

[Aladin Akyurek]

On 2002-04-15 21:50, jonty wrote:
Hallo,
I have set of number say from A1 to A10
say 23 , 343 , 45345334 , 4353453 etc

I need a formula that will make all these number to be 10 digits be adding zeros to the front of the number
ie 0000000023 , 0000000343 , 0045345334 etc

I think i can do it but doing an if statment ie if len(a1)=1, +"000000000"&a1 , ..... but that seems to be a long way around - does anyone know if there is a quicker easier way of doing this

Thanks a lot
Jonty

=RIGHT(REPT("0",10-LEN(A1))&A1,10)

[anno]

On 2002-04-15 21:50, jonty wrote:
Hallo,
I have set of number say from A1 to A10
say 23 , 343 , 45345334 , 4353453 etc

I need a formula that will make all these number to be 10 digits be adding zeros to the front of the number
ie 0000000023 , 0000000343 , 0045345334 etc

I think i can do it but doing an if statment ie if len(a1)=1, +"000000000"&a1 , ..... but that seems to be a long way around - does anyone know if there is a quicker easier way of doing this

Thanks a lot
Jonty

jonty
your LEN idea intrigued me, so just for fun:
=VLOOKUP(LEN(A1),{1,"000000000";2,"00000000";3,"0000000";4,"000000";5,"00000";6,"0000";7,"000";8,"00";9,"0"},2)&A1

[anno]

On 2002-04-15 22:13, Aladin Akyurek wrote:

=RIGHT(REPT("0",10-LEN(A1))&A1,10)

very nice!

[Mark W.]

On 2002-04-15 21:50, jonty wrote:
Hallo,
I have set of number say from A1 to A10
say 23 , 343 , 45345334 , 4353453 etc

I need a formula that will make all these number to be 10 digits be adding zeros to the front of the number
ie 0000000023 , 0000000343 , 0045345334 etc

I think i can do it but doing an if statment ie if len(a1)=1, +"000000000"&a1 , ..... but that seems to be a long way around - does anyone know if there is a quicker easier way of doing this

Thanks a lot
Jonty

This can be easily accomplished with only 1 function call and 1 operator (&)!!!

=RIGHT("0000000000"&A1,10)

[ This Message was edited by: Mark W. on 2002-04-16 07:47 ]