Microsoft Office Tutorials and References
In Depth Information
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-5 shows these formulas in action.
Figure 5-5: 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 the format to fill
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, like this:
$#,##0.00**
See Appendix B for more information about custom number formats, including addi-
tional examples using the asterisk format code.
Changing the case of text
Excel provides three handy functions to change the case of text:
UPPER: Converts the text to ALL UPPERCASE.
Search JabSto ::




Custom Search