# add a 0 in front of 11 digit number to equal 12

#### afs24

##### Board Regular
How can I write a formula that will add zeros in front of a number in another column if that number does not have 12 digits. For example:

My PO is: 66782379167

I want it to be 066782379167.

The number will either have 11 digits or 12 digits. If it's already 12 digits then I don't need to add a zero but if it's 11 digits I need to add the 0.

thanks

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### KMKfan

##### Board Regular
=IF(LEN(cell)<12,"0"&cell,cell)

the formula assumes your value to have 11 digits. If it is possible to have a shorter text, add if statements.

#### wwbwb

##### Well-known Member
=text(a1,"000000000000")

this will make it so that regardless of the size of the value of a1, it will add zeros to make it 12 char long.

#### Norie

##### Well-known Member
Perhaps

=TEXT(A1, REPT("0",12))

Replies
23
Views
697
Replies
2
Views
66
Replies
9
Views
357
Replies
5
Views
297
Replies
2
Views
234

1,195,993
Messages
6,012,745
Members
441,724
Latest member
Aalbid

### 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