Microsoft Office Tutorials and References

In Depth Information

If you're looking for a function that converts a number into spelled-out text (such as

One hundred twelve and 32/100 dollars), you won't find such a function. Well, Excel

does have a function — BAHTTEXT — but it converts the number into the Thai lan-

guage. Why Excel doesn't include an English language version of this function remains

a mystery. VBA can often be used to overcome Excel's deficiencies, though. In Chapter

26, you'll find a custom VBA worksheet function called SPELLDOLLARS, which dis-

plays dollar amounts as English text.

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:

• TRIM removes all leading and trailing spaces, and it replaces internal strings of multiple spaces by a single

space.

• 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

“)

See Chapter 16 for detailed coverage of cleaning up data.

Counting characters in a string

The LEN function takes one argument and returns the number of characters in the argument. For example, as-

sume 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. The LEN function 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

cell A1 contains ten or fewer characters, the entire string is returned: