Text Function Syntax

The file Reggie.xlsx, shown in Figure 5-1, includes examples of text functions. You’ll see how to apply these functions to a specific problem later in the chapter, but let’s begin by describing what each of the text functions does. Then we’ll combine the functions to perform some fairly complex manipulations of data.

Figure 5-1: Examples of text functions

The LEFT Function

The function LEFT(text,k) returns the first k characters in a text string. For example, cell C3 contains the formula LEFT(A3,4). Excel returns Regg.

The RIGHT Function

The function RIGHT(text,k) returns the last k characters in a text string. For example, in cell C4, the formula RIGHT(A3,4) returns ller.

The MID Function

The function MID(text,k,m) begins at character k of a text string and returns the next m characters. For example, the formula MID(A3,2,5) in cell C8 returns characters 2–6 from cell A3, the result being eggie.

The TRIM Function

The function TRIM(text) removes all spaces from a text string except for single spaces between words. For example, in cell C5 the formula TRIM(A3) eliminates two of the three spaces between Reggie and Miller and yields Reggie Miller. The TRIM function also removes spaces at the beginning and end of the cell.

The LEN Function

The function LEN(text) returns the number of characters in a text string (including spaces). For example, in cell C6, the formula LEN(A3) returns 15 because cell A3 contains 15 characters. In cell C7, the formula LEN(C5) returns 13. Because the trimmed result in cell C5 has two spaces removed, cell C5 contains two less characters than the original text in A3.

The FIND and SEARCH Functions

The function FIND(text to find,actual text,k) returns the location at or after character k of the first character of text to find in the actual text. FIND is case sensitive. SEARCH has the same syntax as FIND, but it is not case sensitive. For example, if we enter FIND("r",A3,1) in cell C10, Excel returns 15, the location of the first lowercase r in the text string Reggie Miller. (The uppercase R is ignored because FIND is case sensitive.) Entering SEARCH("r",A3,1) in cell C11 returns 1 because SEARCH matches r to either a lowercase character or an uppercase character. Entering FIND(" ",A3,1) in cell C9 returns 7 because the first space in the string Reggie Miller is the seventh character.

The REPT Function

The REPT function allows you to repeat a text string a desired number of times. The syntax is REPT(text,number of times). For example REPT("|",3) will produce the output |||.

The CONCATENATE and & Functions

The function CONCATENATE(text1,text2, …,text30) can be used to join up to 30 text strings into a single string. The & operator can be used instead of CONCATENATE. For example, entering in cell C12 the formula A1&""&B1 returns Reggie Miller. Entering in cell D12 the formula CONCATENATE(A1," ",B1) yields the same result.

The REPLACE Function

The function REPLACE(old text,k,m,new text) begins at character k of old text and replaces the next m characters with new text. For example, in cell C13, the formula REPLACE(A3,3,2,"nn") replaces the third and fourth characters (gg) in cell A3 with nn. This formula yields Rennie Miller.

The VALUE Function

The function VALUE(text) converts a text string that represents a number to a number. For example, entering in cell B15 the formula VALUE(A15) converts the text string 31 in cell A15 to the numerical value 31. You can identify the value 31 in cell A15 as text because it is left-justified. Similarly, you can identify the value 31 in cell B15 as a number because it is right-justified.

The UPPER and LOWER Functions

The function UPPER(text) will change the text to all uppercase. Thus if cell A1 contains jan then UPPER(A1) will yield JAN. Similarly the function LOWER(text) will change text to lowercase. Thus if A1 contains JAN then LOWER(A1) will yield jan.

The CHAR Function

The function CHAR(number) will yield (for a number between 1 and 255) the ASCII character with that number. For example, CHAR(65) yields A, CHAR(66) yields B, and so on.