Microsoft Office Tutorials and References
In Depth Information
Using SUBSTITUTE and REPLACE to Replace Characters
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 — This is the text in which you want to search for
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.
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.
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 — This is the text or the reference to a cell that contains text
for which you want to substitute characters.
old_text — This is the text you want to replace.
new_text — This is the text you want to replace old_textwith.
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.