Function Substitute()

Substitute()

This function replaces existing text within an old text, with a new text string. Note that this function is case sensitive, i.e. it will treat 'A' and 'a' as different


Syntax:

= Substitute (Base_Text, Replace_Text, New_Text, Instance_num)

Base_text = Is the main text within which a part of the text is to be replaced
Replace_text = Is that part of the Base Text which is to be replaced
New_text = New text string that will replace the old text within the Base Text
Instance_num = The instance of the Replace_text which is to be replaced. This is optional - if it is mentioned, then that instance of the Replace Text gets replaced, else all instances of the Replace Text gets replaced


Example:

For the below examples, let us assume that cell A1 contains text - My name is Excel Matic
  • = Substitute (A1, "Excel Matic", "Mohit Khurana") - result will be - My name is Mohit Khurana
  • = Substitute (A1, "e", "u") - result will be - My namu is Excul Matic
  • = Substitute (A1, "e", "u", 2) - result will be - My name is Excul Matic
Note: In the above examples, note the highlighted text. In example 3 - only the second instance of the letter 'e' has been replaced with 'u'
Share on Google Plus

About Nifty Guru

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.
    Blogger Comment
    Facebook Comment

0 comments:

Post a Comment

Sunday, June 21, 2009

Function Substitute()

Substitute()

This function replaces existing text within an old text, with a new text string. Note that this function is case sensitive, i.e. it will treat 'A' and 'a' as different


Syntax:

= Substitute (Base_Text, Replace_Text, New_Text, Instance_num)

Base_text = Is the main text within which a part of the text is to be replaced
Replace_text = Is that part of the Base Text which is to be replaced
New_text = New text string that will replace the old text within the Base Text
Instance_num = The instance of the Replace_text which is to be replaced. This is optional - if it is mentioned, then that instance of the Replace Text gets replaced, else all instances of the Replace Text gets replaced


Example:

For the below examples, let us assume that cell A1 contains text - My name is Excel Matic
  • = Substitute (A1, "Excel Matic", "Mohit Khurana") - result will be - My name is Mohit Khurana
  • = Substitute (A1, "e", "u") - result will be - My namu is Excul Matic
  • = Substitute (A1, "e", "u", 2) - result will be - My name is Excul Matic
Note: In the above examples, note the highlighted text. In example 3 - only the second instance of the letter 'e' has been replaced with 'u'

Function Substitute()SocialTwist Tell-a-Friend

0 comments:

Post a Comment