Part II: Working with Formulas and Functions
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 11.13 uses this formula to pad the Customer ID fields with zeros:
=LEFT(B4&"0000000000",10)
FIGURE 11.13
Padding Customer ID fields to 10 characters
The formula shown in Figure 11.13 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.
Formatting the numbers in a text string
It’s not uncommon to have reporting that joins text with numbers. For example, you may
have a requirement to display a line in your report that summarizes a salesperson’s results
like this:
John Hutchison: $5,000
The problem is that when you join numbers in a text string, the number formatting does
not follow. Look at Figure 11.14, for example. Note how the numbers in the joined string do
not adopt the formatting of the source cells.
