In this post you will learn how to pad a number with zeros in Excel.
Padding numbers with zeros –
In many cases, the work you do in Excel ends up in other database systems within the
organization. Those database systems often have field length requirements that mandate
a certain number of characters. A common technique for ensuring that a field consists of a
set number of characters is to pad data with zeros.
Padding data with zeros is a relatively easy concept. If you have a Customer ID field that
must be 10 characters long, you essentially would need to add enough zeros to fulfill that
requirement. So, Customer ID 2345 would need be padded with six zeros, making that ID
2345000000.
Cell C4 shown in Figure below uses this formula to pad the Customer ID fields with zeros:
=LEFT(B4&"0000000000",10)
The formula shown in Figure above first joins the value in cell B4 and a text string com-
posed of 10 zeros. This effectively creates a new text string that guarantees a Customer ID
value with 10 zeros.
We then use the LEFT function to extract the left 10 characters of that new text string.