Understanding Text Functions

Text functions in Excel are some of the most useful word-processing and datamanagement tools you’ll find anywhere-they perform tasks word-processing programs can’t do. You’ll find them conveniently listed for you when you click the Text button on the Formulas tab on the Ribbon.

You can use the TRIM and CLEAN functions to remove extra spaces and nonprinting characters, which is great for cleaning up imported data-a task that ranges from difficult to impossible using search and replace. The UPPER, LOWER, and PROPER functions change the case of words, sentences, and paragraphs with no retyping. You might find yourself copying text from other documents into Excel just so you can apply these functions. After using text functions, select the cells containing the formulas, press Ctrl+C to copy, click the Paste button on the Home tab, and then click Paste Values to convert the formulas to their resulting (text) values. You can then copy the edited text into the original document.

In the following sections, we’ll discuss the most useful Excel text functions.

Using Selected Text Functions

Text functions convert numeric entries, as well as numeric text entries, into text strings so you can manipulate the text strings themselves. Numeric text is a type of numeric entry that provides a few specific text characters in addition to numeric characters. For details, see “Using Numeric Text in Formulas” on page 402.

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 hyphen 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.

Note 

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 =CHAR(174).

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 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 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” on page 444.

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.

Using the Substring Text Functions

The following functions locate and return portions of a text string or assemble larger strings from smaller ones: FIND, SEARCH, RIGHT, LEFT, MID, SUBSTITUTE, REPLACE, and CONCATENATE.

The FIND and SEARCH Functions

You use the FIND and SEARCH functions to locate the position of a substring within a string. Both functions return the position in the string of the character you specify. (Excel counts blank spaces and punctuation marks as characters.) These two functions work the same way, except FIND is case sensitive and SEARCH allows wildcards. Both functions take the same arguments: (find_text, within_text, start_num). The optional start_num argument is helpful when within_text contains more than one occurrence of find_text. If you omit start_num, Excel reports the first match it locates. For example, to locate the p in the string A Night At The Opera, you would type the formula =FIND("p", "A Night At The Opera") The formula returns 17, because p is the 17th character in the string.

If you’re not sure of the character sequence you’re searching for, you can use the SEARCH function and include wildcards in your find_text string. Suppose you’ve used the names Smith and Smyth in your worksheet. To determine whether either name is in cell A1, type the formula =SEARCH("Sm?th", A1). If cell A1 contains the text John Smith or John Smyth, the SEARCH function returns the value 6-the starting point of the string Sm?th.

If you’re not sure of the number of characters, use the * wildcard. For example, to find the position of Allan or Alan within the text (if any) stored in cell A1, type the formula =SEARCH("A*an", A1).

The RIGHT and LEFT Functions

The RIGHT function returns the rightmost series of characters from a specified string; the LEFT function returns the leftmost series of characters. These functions take the same arguments: (text, num_chars). The num_chars argument indicates the number of characters to extract from the text argument.

These functions count blank spaces in the text argument as characters; if text contains leading or trailing blank characters, you might want to use a TRIM function within the RIGHT or LEFT function to ensure the expected result. For example, suppose you type This is a test in cell A1 on your worksheet. The formula =RIGHT(A1, 4) returns the word test.

The MID Function

You can use the MID function to extract a series of characters from a text string. This function takes the arguments (text, start_num, num_chars). For example, if cell A1 contains the text This Is A Long Text Entry, you can type the formula =MID(A1, 11, 9) to extract the characters Long Text from the entry in cell A1.

The REPLACE and SUBSTITUTE Functions

The REPLACE and SUBSTITUTE functions substitute new text for old text. The REPLACE function replaces one string of characters with another string of characters and takes the arguments (old_text, start_num, num_chars, new_text). Suppose cell A1 contains the text Eric Miller, CEO. To replace the first four characters with the string Geof, type the formula =REPLACE(A1, 1, 4, "Geof"). The result is Geof Miller, CEO.

With the SUBSTITUTE function, you specify the text to replace. The function takes the arguments (text, old_text, new_text, instance_num). Suppose cell A1 contains the text Mandy and you want to place it in cell A2 but change it to Randy. Type =SUBSTITUTE(A1, "M", "R") in cell A2.

The instancc_num argument optionally replaces only the specified occurrence of old_text. For example, if cell A1 contains the text through the hoop, the 4 in the formula =SUBSTITUTE(A1, "h", "l", 4) tells Excel to substitute an 1 for the fourth h found in cell A1. If you don’t include instance_num, Excel changes all occurrences of old_text to new_text.

Note 

You can create an array formula using the SUBSTITUTE function to count the number of occurrences of a text string in a range of cells. Use the formula =SUM(LEN(<range>)LEN(SUBSTITUTE(<range>, "text", "")))/LEN("text") to count the number of times text appears in <range>. Type the formula, and press Ctrl+Shift+Enter.

The CONCATENATE Function

To assemble strings from up to 255 smaller strings or references, the CONCATENATE function is the function equivalent of the & character. For example, if cell B4 contains the text Pacific with a trailing space character, the formula =CONCATENATE(B4, "Musical Instruments") returns Pacific Musical Instruments.

Troubleshooting

Concatenated dates become serial numbers.

If you try to concatenate the contents of a cell formatted as a date, the result is probably not what you expect. Because a date in Excel is only a serial number, what you usually see is a formatted representation of the date. But when you concatenate the contents of a date-formatted cell, you get the unformatted version of the date. To avoid this problem, use the TEXT function to convert the serial number to a recognizable form. For example, suppose cell A1 contains the text Today’s Date is and cell A2 contains the function = NOW() and is formatted to display the date in dd/mm/yyyy format. Nonetheless, the formula =CONCATENATE(A1, " ", A2) results in the value Today’s Date is 39511 (or whatever the current date serial number happens to be). To remedy this problem, type the TEXT function as follows: =CONCATENATE(A1, " ", TEXT(A2, "dd/mm/yyyy")).

This version returns the value Today’s Date is 03/04/2008 (or whatever today’s date happens to be). Note that the formula includes a space character as a separate argument (" ") between the two cell reference arguments.