Microsoft Office Tutorials and References
In Depth Information
Mastering Excel’s Built-in Functions
The simplest functions are useful for removing unwanted characters from text: CLEAN and
TRIM take a single argument (text) and remove nonprintable characters and spaces,
respectively, from the string in the referenced location.
If your imported text uses inconsistent or inappropriate case formatting, you can use one of
three functions to change it. UPPER, LOWER, and PROPER also use a single text argument.
The effect is to change the referenced text to all capitals, all lowercase, or caps for the first
letter in each word, respectively. The example shown here illustrates the PROPER function in
action, transforming a column of text in all capital letters to easier-to-read initial caps.
That example also illustrates one of the problems with the PROPER function. It blindly
capitalizes every word in the source text, even when those words are articles and conjunctions
that shouldn’t be capitalized in a title. It also automatically capitalizes the first letter after
an apostrophe. That can lead to some inconsistent (and unwanted) results with possessives:
FINNEGAN’S WAKE becomes Finnegan’S Wake, for example.
Another group of functions allows you to convert a value to text. This is a necessary step
if you have a cell that contains a number and you want to concatenate that number with
some text. Here are three useful functions in this group:
● DOLLAR(number,decimals) This function converts a number to text using the $
(dollar) currency format. The second argument is optional; by default, Excel formats
the number using two decimal places. If cell B5 contains the value 24.5, the formula
=DOLLAR(B5) returns the text string $24.50.
● FIXED(number,decimals,no_commas) Use this function to display a number
as text, rounded to a fixed number of decimals. Only the first argument (number)