Excel First

Excel Solutions

SUBSTITUTE function: Basic and Advanced Examples

SUBSTITUTE function type: Text

Description: Substitutes the old text with new text in a text string

Beyond 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:

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 only

Text 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 delimiter

Example 1: Initial text string: SKU-22B7-99F5

Note: 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: 99F5

Method: 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 position

Example 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.
 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

The Excel Learning Journey that I propose for you, is structured in such a way as to offer you the most practical way to learn Excel, free.

Start Your Free Excel Learning Journey!

Enter your email address to subscribe to this blog: