Chapter 11: Using Formulas to Manipulate Text
11
FIGURE 11.11
Using the CHAR() function to force a line break between the sales rep name and the data
value
You can call up any character in a formula by using the CHAR() function. In the example
shown in Figure 11.11, we are calling up the line break character and joining it with the
values in cells A3 and C3:
=A3&CHAR(10)&C3The cell itself won’t show the line break unless you have applied wrap text. But even if you
haven’t, any chart using this kind of formula will display the data returned by the formula
with the line breaks.
Cleaning strange characters from text fields
When importing data from external data sources like text files or web feeds, strange char-
acters may come in with your data. Instead of trying to clean these up manually, you can
use Excel’s CLEAN function (see Figure 11.12).
FIGURE 11.12
Cleaning data with the CLEAN function
The CLEAN function removes nonprintable characters from any text you pass to it. You can
wrap the CLEAN function within the TRIM function to remove unprintable characters and
excess spaces at the same time.
=TRIM(CLEAN(B3))