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

afs24

Board Regular
Joined
Sep 26, 2002
Messages
237
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
 

KMKfan

Board Regular
Joined
Mar 8, 2004
Messages
106
=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
Joined
Oct 20, 2003
Messages
513
=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.
 

Forum statistics

Threads
1,077,782
Messages
5,336,291
Members
399,074
Latest member
rlong98

Some videos you may like

This Week's Hot Topics

Top