# zero fills

#### jdoiron

##### New Member
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.

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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

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

Another formula option is

=TEXT(A1,"0000000000")

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

=RIGHT("0000000000"&A1,10)

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

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

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.

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

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

If you just need it to look as if there are leading zeros, use a custom format with 10 zeros...

Replies
1
Views
116
Replies
8
Views
240
Replies
15
Views
479
Replies
15
Views
562
Replies
4
Views
133

1,203,396
Messages
6,055,161
Members
444,766
Latest member
bryandaniel5

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back