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'

About Dhakkanz

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.

Sunday, June 21, 2009