Chapter 11: Creating Formulas That Manipulate Text
239
Cross-Reference
See Chapter 24 for more information about custom number formats, including additional examples using the
asterisk format code. n
Removing excess spaces and nonprinting characters ...............................................
Often, data imported into an Excel worksheet contains excess spaces or strange (often unprintable)
characters. Excel provides you with two functions to help whip your data into shape: TRIM and
CLEAN:l TRIM removes all leading and trailing spaces and replaces internal strings of multiple
spaces by a single space.
l 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 ................................................................................
The LEN function takes one argument and returns the number of characters in the argument. For
example, assume that the string September Sales is contained in cell A1. The following formula
returns 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-Reference
You see example formulas that demonstrate how to count the number of specific characters within a string
later in this chapter. Chapter 13 covers counting techniques further. n
Changing the case of text ........................................................................................
Excel provides three handy functions to change the case of text:l (^) UPPER converts the text to ALL UPPERCASE.
l LOWER converts the text to all lowercase.
l (^) PROPER converts the text to Proper Case (the first letter in each word is capitalized, as in
a proper name).