Using Selected Text Functions

USING SELECTED TEXT FUNCTIONS

Text functions convert numeric text entries into numbers and convert number entries into text strings so that you can manipulate the text strings themselves.

The TEXT Function

The TEXT function converts a number into a text string with a specified format. Its arguments are (value, format_text), where value represents any number, formula, or cell reference; and format_text is the format for displaying the resulting string. For example, the formula =TEXT(98/4,"0.00") returns the text string 24.50. You can use any Excel formatting symbol ($, #, 0, and so on) except the asterisk (*) to specify the format you want, but you can’t use the General format.

The DOLLAR Function

Like the TEXT function, the DOLLAR function converts a number into a string. DOLLAR, however, formats the resulting string as currency with the number of decimal places you specify. The arguments (number, decimals) specify a number or reference and the number of decimal places you want. For example, the formula =DOLLAR(45.899, 2) returns the text string $45.90. Notice that Excel rounds the number when necessary. If you omit decimals, Excel uses two decimal places. If you add a comma after the first argument but omit the second argument, Excel uses zero decimal places. If you use a negative number for decimals, Excel rounds to the left of the decimal point.

The LEN Function

The LEN function returns the number of characters in an entry. The single argument can be a number, a string enclosed in double quotation marks, or a reference to a cell. Trailing zeros are ignored.

For example, the formula =LEN("Test") returns 4. The LEN function returns the length of the displayed text or value, not the length of the underlying cell contents. For example, suppose cell A10 contains the formula =A1+A2+A3+A4+A5+A6+A7+A8 and its result is the value 25. The formula =LEN(A10) returns the value 2, which indicates the length of the resulting value 25. The cell referenced as the argument of the LEN function can contain another string function. For example, if cell A1 contains the function =REPT("-*", 75), which enters the two-character dash and asterisk string 75 times in a cell, the formula =LEN(A1) returns the value 150.

The ASCII Functions: CHAR and CODE

Every computer uses numeric codes to represent characters. The most prevalent system of numeric codes is ASCII, or American Standard Code for Information Interchange. ASCII uses a number from 0 to 127 (or in some systems, to 255) to represent each number, letter, and symbol.

The CHAR and CODE functions deal with these ASCII codes. The CHAR function returns the character that corresponds to an ASCII code number; the CODE function returns the ASCII code number for the first character of its argument. For example, the formula =CHAR(83) returns the text S. The formula =CODE("S") returns the ASCII code 83. If you type a literal character as the text argument, be sure to enclose the character in quotation marks; otherwise, Excel returns the #NAME? error value.

The Clean-Up 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 between words.

The CLEAN function is similar to TRIM, except it operates on only 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 than 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 of your worksheet both contain the text Totals, the formula =EXACT(A5, A6) returns TRUE.

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.

Unexpected results can occur when the text contains punctuation, however. For example, if cell A1 contains the text “it wasn’t bad,” the PROPER function converts it to It Wasn’T Bad.