Microsoft Office Tutorials and References
In Depth Information
Using the substring text functions
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 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 .
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 as many as 255 smaller strings or references, the CONCATENATE
function is the function equivalent of the & character. For example, if cell B4 contains the
text Salt , the formula =CONCATENATE(B4, " of the Earth") returns Salt of the Earth . Note
that we included a leading space character in the text argument; otherwise, the result
would be Saltof the Earth . You could also specify the space character as a separate
argument, as in the formula =CONCATENATE(B4, " ", "of the Earth").