Using the Substring Text Functions

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 seventeenth 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 of 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 this formula in cell A2 =SUBSTITUTE(A1,"M","R").

The instance_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 l 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.

The CONCATENATE Function

To assemble strings from up to 30 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".