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