Microsoft Office Tutorials and References

In Depth Information

**Text Functions**

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:

h
TRIM removes all leading and trailing spaces, and it replaces internal strings of multiple

spaces by a single space.

h
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 Excel LEN function takes one argument and returns the number of characters in the

argument. For example, assume that cell A1 contains the string
September Sales.
The following

formula returns 15:

=LEN(A1)

Notice that space characters are included in the character count. This can be useful for

identifying strings with extraneous spaces — which can cause problems in some situations, such as in

lookup formulas. The following formula returns FALSE if cell A1 contains any leading spaces,

trailing spaces, or multiple spaces.

=LEN(A1)=LEN(TRIM(A1))

The following formula shortens text that is too long. If the text in A1 is more than ten characters

in length, this formula returns the first nine characters plus an ellipsis (133 on the ANSI chart) as a

continuation character. If it’s ten or fewer, the whole string is returned:

=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”

section). Also, Chapter 7 contains additional counting techniques. Still more counting

examples are provided in Chapter 15, which deals with array formulas.