Microsoft Office Tutorials and References
In Depth Information
Using SUBSTITUTE and REPLACE to Replace Characters
Caution
If find_textdoes not appear in within_text, FIND returns a #VALUE! er-
ror. If start_numis not greater than zero, FIND returns a #VALUE! er-
ror. If start_numis greater than the length of within_text, FIND re-
turns a #VALUE! error.
within_text
within_text This is the text in which you want to search for
find_text.
start_num
start_num This is the character number in within_textat which you
want to start searching. If start_numis omitted, it is assumed to be 1.
If start_numis not greater than zero or is greater than the length of
within_text, a #VALUE! error is returned.
Using
Using SUBSTITUTE
SUBSTITUTE and
and REPLACE
REPLACE to Replace Characters
to Replace Characters
When you have the ability to find text, you might want to replace text. Excel
offers two functions for this: SUBSTITUTE and REPLACE. The SUBSTITUTE
function is easier to use and should be your first approach.
Syntax
=SUBSTITUTE(text,old_text,new_text,instance_num)
The SUBSTITUTE function substitutes new_textfor old_textin a text string.
You use SUBSTITUTE when you want to replace specific text in a text string;
you use REPLACE when you want to replace any text that occurs in a spe-
cific location in a text string.
The SUBSTITUTE function takes the following arguments:
text
text This is the text or the reference to a cell that contains text
for which you want to substitute characters.
old_text
old_text This is the text you want to replace.
new_text
new_text This is the text you want to replace old_textwith.
instance_num
instance_num This specifies which occurrence of old_textyou want
to replace with new_text. If you specify instance_num, only that in-
stance of old_textis replaced. Otherwise, every occurrence of
old_textin text is changed to new_text.
Search JabSto ::




Custom Search