SUBSTITUTE function type: Text

Description: Substitutes the old text with new text in a text stringBeyond its basic use, which is fairly simple, the SUBSTITUTE function can be very useful combined with other functions, to get your desired results. Take a look at the advanced applications examples:Applications:

• Substitute last occurrence only
• Extract partial string after last delimiter
• Extract item from delimited text, based on item position

Download the SUBSTITUTE function demo: SUBSTITUTE.xlsx Function Arguments:=SUBSTITUTE(text, old_text, new_text, [instance_num])

Argument	Description
text	The text or the reference to a cell containing text for which you want to substitute characters.
old_text	The text you want to replace.
new_text	Replacement text for old_text
[instance_num]	Optional. Allows you to specify which occurrence should be replaced. If this argument is ommited, every occurrence of old_text will be replaced with new_text.

 Note: If you are already familiar with basic use of this Function, click here to  Go To Advanced Examples. Basic Example: Text to process: “Mary has a cat. Her cat is 1 year old.”Replace all occurences (instance_num argument is omitted):=SUBSTITUTE(“Mary has a cat. Her cat is 1 year old.”,“cat”,“dog”) Result: Mary has a dog. Her dog is 1 year old.Replace first occurence:=SUBSTITUTE(“Mary has a cat. Her cat is 1 year old.”,“cat”,“dog”,1) Result: Mary has a dog. Her cat is 1 year old.You can always use cell references to pass them to function arguments. For example, if cell A1 contains the text argument: “Mary has a cat. Her cat is 1 year old.”, A2 has the old_text argument: “cat” and A3 has the replacement (new_text argument) – “dog”:=SUBSTITUTE(A1, A2, A3, 1) 

Advanced Examples:

 Application 1: Substitute last occurrence onlyText to process: “Mary has a cat. Her cat is 1 year old.”old_text: “cat”new_text: “dog”instance_num: formula to calculate the number of occurences of the old_text in the text argument.Formula for instance_num:=(LEN(“Mary has a cat. Her cat is 1 year old.”)-LEN(SUBSTITUTE(“Mary has a cat. Her cat is 1 year old.”,”cat”,””)))/LEN(“cat”)Method used: By deducting from the length of the initial text the length of the string where the old_text is replaced with a zero length string (“”), the result will be the number of old_text occurences.Note: if the old_text has more than 1 character, you have to divide the result with /LEN(“cat”), otherwise the result is wrong.Full Formula:=SUBSTITUTE(“Mary has a cat. Her cat is 1 year old.”,“cat”,“dog”, (LEN(“Mary has a cat. Her cat is 1 year old.”)-LEN(SUBSTITUTE(“Mary has a cat. Her cat is 1 year old.”,”cat”,””)))/LEN(“cat”))Result: “Mary has a cat. Her dog is 1 year old.” Application 2: Extract partial string after last delimiterExample 1: Initial text string: SKU-22B7-99F5Note: the initial string is stored in cell A1, this reference will be used in all examples.Target: extract the last item from the initial string: 99F5Method: replace the last “-” delimiter with a unique delimiter, “^” in this example, (which is not present in the original string), then identify this new delimiter in the new string (with the last delimiter replaced). The last step is to extract the last part of the string using the RIGHT function, based on the position of the last delimiter replaced, determined with FIND function.Step 1: Replace last delimiter:=SUBSTITUTE(A1,”-“,”^”,LEN(A1)-LEN(SUBSTITUTE(A1,”-“,””))) Step 1 Result: SKU-22B7^99F5 Step 2: Extract the string after the last delimiter:=RIGHT(A1,LEN(A1)-FIND(“^”,SUBSTITUTE(A1,”-“,”^”,LEN(A1)-LEN(SUBSTITUTE(A1,”-“,””))))) Step 2 Result: 99F5 Example 2: Initial text string: http://www.fonearena.com/blog/87685/lenovo-p780-camera-samples.html Method: replace the last “/” delimiter with a unique delimiter: “^” , identify the new delimiter in the new string and extract the last part, after the last delimiter “/”:Step 1: Replace last delimiter:=SUBSTITUTE(A1,”/”,”^”,LEN(A1)-LEN(SUBSTITUTE(A1,”/”,””))) Step 1 Result: http://www.fonearena.com/blog/87685^lenovo-p780-camera-samples.html Step 2: Extract the string after the last delimiter:=RIGHT(A1,LEN(A1)-FIND(“^”,SUBSTITUTE(A1,”/”,”^”,LEN(A1)-LEN(SUBSTITUTE(A1,”/”,””))))) Step 2 Result: lenovo-p780-camera-samples.html Application 3: Extract item from delimited text, based on item positionExample 1: Initial text string: first-/second-/third-/fourth-/fifth-/sixth-/seventh Take a look at the following 3 formulas, each has a different approach. The most simple and efficient approach is the last formula, the credit goes to Rich Rothstein for developing this solution.The only difference is that in Rich Rothstein’s formula, the list is in reversed order, the count starts from the last item from the delimited text string. I built the first 2 formulas just to show you that there can be multiple solutions to the same problem, you can build your own version. Formula 1 (A1 has the text, A2 contains a number corresponding to the position of the desired item):=MID(“-/”&A1&”-/”,FIND(“^”,SUBSTITUTE(“-/”&A1&”-/”,”-/”,”^”,A2))+LEN(“-/”),FIND(“^”,SUBSTITUTE(“-/”&A1&”-/”,”-/”,”^”,A2+1))-FIND(“^”,SUBSTITUTE( “-/”&A1&”-/”, “-/”,”^”, A2)) -LEN(“-/”)) Formula 2 is the “ugliest” approach, you will find it only in the attached workbook.The interesting part in this approach is the use of an array of values to instance_num argument of the SUBSTITUTE function, will return an array of results. The array of results is used in FIND function: FIND(“^”,SUBSTITUTE(“-/”&D62&”-/”,“-/”,“^”,{1;2;3;4;5;6;7;8}))FIND function will return this array: {1;8;16;23;31;38;45;54}, which is the position of ALL delimiters (including the additional delimiters).The array of values for instance_num argument is not hard typed, it is dynamically created by this part of the formula: ROW(A1:INDEX(A1:A100,(LEN(A1)-LEN(SUBSTITUTE(A1,”-/”,””)))/LEN(“-/”)+2)) Formula 3 (A1 has the text, A2 contains a number corresponding to the position of the desired item, from last to first):=TRIM(LEFT(RIGHT(SUBSTITUTE(“-/”&A1,”-/”,REPT(” “,99)),A2*99),99)) The result is in reversed order: 1 means the last item Formula 3 is developed by Rich Rothstein.