Part II: Working with Formulas and Functions
Wrapping the SUBSTITUTE function in a LEN function gives us the length of the text
without the spaces. Note that we have to add one (+1) to that answer to account for the
fact that the last word will not have an associated space:
LEN(SUBSTITUTE(B4," ",""))+1
Subtracting the adjusted length from the original length gives us our word count:
=LEN(B4)-LEN(SUBSTITUTE(B4," ",""))+1
Adding a line break within a formula
When creating charts in Excel, it’s sometimes useful to force line breaks for the purpose of
creating better visualizations. Take the chart shown in Figure 11.10, for example. Here you
see a chart where the x-axis labels include the data value for each sales rep. This comes in
handy when you don’t want to inundate your chart with data labels.
FIGURE 11.10
The x-axis labels in this chart include a line break and a reference to the data values.
The secret to this trick is the use of the CHAR() function within a formula that makes up
your chart labels (see Figure 11.11).
Every character in Excel has an associated American National Standards Institute (ANSI)
character code. The ANSI character code is a Windows system code set that defines the
characters you see on your screen. The ANSI character set consists of 255 characters, num-
bering from 1 to 255. The uppercase letter A is character 65. The number 9 is character 57.
Even nonprinting characters have codes. The code for a space is 32. The code for a line
break is 10.
