The Excel Substitute function
replaces one or more instances of a given text string, within an original text
string.
Syntax
The syntax for the SUBSTITUTE
function in Microsoft Excel is:
SUBSTITUTE( text, old_text, new_text,
[Instance] )
Parameters or Arguments
text
The original string to use to
perform the substitution.
old_text
The existing characters to
replace.
new_text
The new characters to replace
old_text with.
Instance
Optional. It is the nth
appearance of old_text that you wish to replace. If this parameter is omitted,
then every occurrence of old_text will be replaced with new_text.
Substitute Function Examples
Column B of the spreadsheet
below shows four examples of the Excel Substitute Function.
Formulas:
|
|
A
|
B
|
|
1
|
abab
|
=SUBSTITUTE(
A1, "a", "X" )
|
|
2
|
abab
|
=SUBSTITUTE(
A2, "a", "X", 2 )
|
|
3
|
John is
5 years old
|
=SUBSTITUTE(
A3, "John", "Jack" )
|
|
4
|
John is
5 years old
|
=SUBSTITUTE(
A4, "5", "6" )
|
Results:
|
|
A
|
B
|
|
1
|
abab
|
XbXb
|
|
2
|
abab
|
abXb
|
|
3
|
John is
5 years old
|
Jack is
5 years old
|
|
4
|
John is
5 years old
|
John is
6 years old
|
Note:
1. Use
the Substitute function when you want to replace text based on its content, not
position.
2. Substitute
finds and replaces old_text with new_text in a text string. Instance limits Substitute
replacement to one particular instance of old_text. If instance is not
supplied, all instances of old_text are replaced with new_text.
3. instance
is optional. if not supplied, all instances of old_text are replaced with
new_text.
4. Substitute is case-sensitive and does not support wildcards.
5. The Excel Substitute function is designed
for use with text strings and returns a text string. Therefore, if you attempt
to use the substitute function with a date, time or a number, it may return
unexpected results.
Reference:
follow us on
http://arivilm.blogspot.in
like us on https://www.facebook.com/Arivilm2501/
No comments:
Post a Comment