Set the length of a number

[Sophie]

Hello Everyone!

I have a column that needs to be populated with a six
digit number. I start off with a number (in cell G2) that
can be between 1 and four digits in length. I have used
the function below.

=IF(LEN(G2)=1,"00000"&G2,IF(LEN(G2)=2,"0000"&G2,IF(LEN(G2)
=3,"000"&G2,IF(LEN(G2)=4,"00"&G2,"Not"))))

It appears I might need expand this out to 13 digits. Can
anyone think of a better way as this formula will start
to get clumsy

TIA
Sxx

 

[Ron de Bruin]

Hi Sophie

Select the cells
Ctrl-1
Choose Custom
Type 000000

 

[Govind]

Hi,

Use

=IF(LEN(G2)<13,REPT(0,13-LEN(G2))&G2,G2)

Regards

Govind.

 

[sulprobil]

Wouldn't =TEXT(G2,"0000000000000") help?

Regards,
sulprobil

 

[Mike Lewis]

Hi Sophie,

This is one way:

=REPT("0",13-LEN(D4))&D4

where d4 is the number and 13 is the required length

Good Luck
Mike

 

[Guest]

Thanks Ron

Not the appearance I'm really trying to change
More the number of 0's at the beginning in the new number
As per Mike's reply

Thank you though

S

-----Original Message-----
Hi Sophie

Select the cells
Ctrl-1
Choose Custom
Type 000000

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Sophie" <[email protected]> wrote in

message news:[email protected]...

 

[Guest]

Hi Sulprobil

Works nicely, thank you very much indeed

S

 

[Guest]

Hi Mike

Very nice!
Thank you very much indeed

S

-----Original Message-----
Hi Sophie,

This is one way:

=REPT("0",13-LEN(D4))&D4

where d4 is the number and 13 is the required length

Good Luck
Mike

.

 

[Guest]

Hi Govind

Very nice!
Thank you very much indeed

S