Microsoft Office Tutorials and References
In Depth Information
=IF(LEN(A1)>10,LEFT(A1,9)&CHAR(133),A1)
Later in this chapter, you'll see example formulas that demonstrate how to count the
number of a specific character within a string (see the “Advanced Text Formulas” sec-
tion). Also, Chapter 7 contains additional counting techniques. Still more counting ex-
amples are provided in Chapter 15, which deals with array formulas.
Repeating a character or string
The REPT function repeats a text string (first argument) any number of times you specify (second argument).
For example, this formula returns HoHoHo:
=REPT(“Ho”,3)
You can also use this function to create a crude horizontal divider between cells. This example displays a squig-
gly line, 20 characters in length:
=REPT(“~”,20)
Creating a text histogram
A clever use for the REPT function is to create a simple histogram (also known as a frequency distribution ) dir-
ectly in a worksheet (chart not required). Figure 5-4 shows an example of such a histogram. You'll find this type
of graphical display especially useful when you need to visually summarize many values. In such a case, a
standard chart may be unwieldy.
The data bars conditional formatting feature is a much better way to display a simple
histogram directly in cells. See Chapter 19 for more information about data bars.
The formulas in column D graphically depict the values in column B by displaying a series of characters in the
Wingdings 2 font. This example uses character code 162, which displays as a solid rectangle in the Wingdings 2
font. A formula using the REPT function determines the number of characters displayed. Cell D2 contains this
formula:
=REPT(CHAR(162),B2/100)
Search JabSto ::




Custom Search