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
• 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):
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:
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.
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: