Microsoft Office Tutorials and References

In Depth Information

**Text Functions**

Assign the Wingdings font to cells E3 and G3, and then copy the formulas down the columns to

accommodate all the data. Right-align the text in column E and adjust any other formatting.

Depending on the numerical range of your data, you may need to change the scaling. Experiment

by replacing the 100 value in the formulas. You can substitute any character you like for the
n in

the formulas to produce a different character in the chart.

The workbook shown in Figure 5-3,
text histogram.xlsx
, also appears on the

companion CD-ROM.

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 24 characters total:

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

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

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

The following formula displays asterisk padding on both sides of the number. It returns 24

characters when the number in cell A1 contains an even number of characters; otherwise, it returns 23

characters.

=REPT(“*”,12-LEN(A1)/2)&A1&REPT(“*”,12-LEN(A1)/2)

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

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

the left:

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

Figure 5-4 shows this formula in action.