Excel 2010 Bible

Part II: Working with Formulas and Functions

238

On the CD
The workbook shown in Figure 11.3 also appears on the companion CD-ROM. The file is named text
histogram.xlsx.

Padding a number ...................................................................................................

You’re probably familiar with a common security measure (frequently used on printed checks) in

which numbers are padded with asterisks on the right. The following formula displays the value in

cell A1, along with enough asterisks to make a total of 24 characters:

=(A1 & REPT(“*”,24-LEN(A1)))

If you’d prefer to pad the number with asterisks on the left instead, use this formula:

=REPT(“*”,24-LEN(A1))&A1

The following formula displays 12 asterisks on both sides of the number:

=REPT(“*”,12)&A1&REPT(“*”,12)

The preceding formulas are a bit deficient because they don’t show any number formatting. This

revised version displays the value in A1 (formatted), along with the asterisk padding on the right:

=(TEXT(A1,”$#,##0.00”)&REPT(“*”,24-LEN(TEXT(A1,”$#,##0.00”))))

Figure 11.4 shows this formula in action.

FIGURE 11.4

Using a formula to pad a number with asterisks.

You can also pad a number by using a custom number format. To repeat the next character in that

format until it fills the column width, include an asterisk (*) in the custom number format code.

For example, use this number format to pad the number with dashes:

$#,##0.00*-

To pad the number with asterisks, use two asterisks in the number-format code, like this:

$#,##0.00**