zero fills

[jdoiron]

How do I zero fill a column so there a 10 digits in it. Say the column has the value of 2345, I need it to be 0000002345.

 

[PaddyD]

Hi,

If you just need it to look as if there are leading zeros, use a custom format with 10 zeros. If you need the zeros as data, use the rept function - if A1 holds 1234, put

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

Paddy

 

[Aladin Akyurek]

On 2002-08-21 15:53, jdoiron wrote:
How do I zero fill a column so there a 10 digits in it. Say the column has the value of 2345, I need it to be 0000002345.

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

 

[Juan Pablo González]

Another formula option is

=TEXT(A1,"0000000000")

 

[Mark W.]

I like Juan's... but should we go for one more...

=RIGHT("0000000000"&A1,10)

 

[parry]

Hi its better to format the cells you need as REPT,TEXT & RIGHT turns the values into text, so if you tried to add the values it wont work.

Instead, select the column you want formated, select Format-Cells-Custom and in the Type box enter 0000000000 the click OK. This will put high order zero's in for you.
This message was edited by parry on 2002-08-21 16:06

 

[Juan Pablo González]

Hi Parry, welcome to the board. And yes, but that depends... also, see PaddyD's first suggestion...

 

[Mark W.]

On 2002-08-21 16:05, parry wrote:
Hi its better to format the cells you need as REPT,TEXT & RIGHT turns the values into text, so if you tried to add the values it wont work.

Instead, select the column you want formated, select Format-Cells-Custom and in the Type box enter 0000000000 the click OK. This will put high order zero's in for you.
This message was edited by parry on 2002-08-21 16:06

Unless "0000002345" is needed for a match or lookup.

 

[parry]

Thanks Juan. I thought ampersand meant concatenate which always turned the value into text (he has &a1). Am I wrong?

 

[Juan Pablo González]

Np Parry, you're not wrong, but I meant this suggestion... also, you can convert a string to a number simply enough like this

="1234"+0

On 2002-08-21 15:58, PaddyD wrote:
If you just need it to look as if there are leading zeros, use a custom format with 10 zeros...