Page 257 - Excel 2007 Bible

17_044039 ch12.qxp 11/21/06 11:05 AM Page 214
Part II
Working with Formulas and Functions
To pad the number with asterisks, use two asterisks in the number-format code, like this:
$#,##0.00**
Refer to Chapter 24 for more information about custom number formats, including additional
CROSS-REF
CROSS-REF
examples using the asterisk format code.
Removing excess spaces and nonprinting characters
Often data imported into an Excel worksheet contains excess spaces or strange (often unprintable) charac-
ters. Excel provides you with two functions to help whip your data into shape: TRIM and CLEAN:
n TRIM removes all leading and trailing spaces and replaces internal strings of multiple spaces by a
single space.
n CLEAN removes all nonprinting characters from a string. These “garbage” characters often appear
when you import certain types of data.
This example uses the TRIM function. The formula returns Fourth Quarter Earnings (with no excess
spaces):
=TRIM(“ Fourth Quarter Earnings “)
Counting characters in a string
Excel’s LEN function takes one argument and returns the number of characters in the argument. For exam-
ple, assume the string September Sales is contained in cell A1. The following formula will return 15:
=LEN(A1)
Notice that space characters are included in the character count.
The following formula returns the total number of characters in the range A1:A3:
=LEN(A1)+LEN(A2)+LEN(A3)
CROSS-REF You see example formulas that demonstrate how to count the number of specific characters
CROSS-REF
within a string later in this chapter. Chapter 14 covers counting techniques further.
Changing the case of text
Excel provides three handy functions to change the case of text:
n UPPER converts the text to ALL UPPERCASE.
n LOWER converts the text to all lowercase.
n PROPER converts the text to Proper Case (the first letter in each word is capitalized, as in a proper
name).
These functions are quite straightforward. The formula that follows, for example, converts the text in cell
A1 to proper case.
=PROPER(A1)
If cell A1 contained the text MR. JOHN Q. PUBLIC, the formula would return Mr. John Q. Public.

214