Microsoft Office Tutorials and References
In Depth Information
Understanding text functions
If you use certain ASCII symbols often, you can use the ASCII code number with the
CHAR function to create a symbol without using the Symbol button on the Insert tab
on the ribbon. For example, to create a registered trademark symbol (®), just type
The cleanup functions: TRIM and CLEAN
Leading and trailing blank characters often prevent you from correctly sorting entries in a
worksheet or a database. If you use string functions to manipulate text in your worksheet,
extra spaces can prevent your formulas from working correctly. The TRIM function
eliminates leading, trailing, and extra blank characters from a string, leaving only single spaces
The CLEAN function is similar to TRIM, except it operates only on nonprintable characters,
such as tabs and program-specific codes. CLEAN is especially useful if you import data from
another program or operating system, because the translation process often introduces
nonprintable characters that appear as symbols or boxes. You can use CLEAN to remove
these characters from the data.
The EXACT function
The EXACT function is a conditional function that determines whether two strings match
exactly. The function ignores formatting, but it is case sensitive, so uppercase letters are
considered different from lowercase letters. If both strings are identical, the function returns
TRUE. Both arguments must be literal strings enclosed in quotation marks, references to
cells that contain text, numeric values, or formulas that evaluate to numeric values. For
example, if cell A5 and cell A6 on your worksheet both contain the text Totals , the formula
=EXACT(A5, A6) returns TRUE.
For information about comparing strings, see “Creating conditional tests” in Chapter 12.
The case functions: UPPER, LOWER, and PROPER
Three functions manipulate the case of characters in text strings. The UPPER and LOWER
functions convert text strings to all uppercase or all lowercase letters. The PROPER
function capitalizes the first letter in each word, capitalizes any other letters in the text string
that do not follow another letter, and converts all other letters to lowercase. For example,
if cell A1 contains the text mark Dodge , you can type the formula =UPPER(A1) to return
MARK DODGE . Similarly, the formula =LOWER(A1) returns mark dodge , and =PROPER(A1)
returns Mark Dodge .