Use of SUBSTITUTE function/Formula with Example in Microsoft Excel.

Use of SUBSTITUTE function/Formula with Example in Microsoft Excel.



SUBSTITUTE is a great function/Formula in MS Excel that you may not have aware of, but now that you are about to learn, you will probably find everyday uses for it in Data Analysis.

In Microsoft Excel, you use the SUBSTITUTE function/Formula when you wish to replace specific text in a text string.

There are three pieces of information that are required for the SUBSTITUTE function/Formula in which a fourth is optional.

SUBSTITUTE SYNTAX:-

SUBSTITUTE(text,old_text, new_text, instance_num)
 

Text is the text string or cell containing text for which you want to substitute characters.
 

Old_text is the text you want to replace.
 

New_text is the text you want to replace it with.
 

Instance_num [Optional] specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

Examples of SUBSTITUTE Function:-

Example 1:
Replace the 2nd occurrence of - (hyphen) with / (Slash)

Formula:   =SUBSTITUTE("ABC-123-456-789","-","/",2)
Result:      ABC-123/456-789

Example 2:
In "ABC-123-456-789" replace all occurrences of - (hyphen) with / (Slash)

Formula:   =SUBSTITUTE("ABC-123-456-789","-","/")
Result:      ABC/123/456/789

Because the instance number (i.e. 4th piece of information) is omitted from the function/Formula, Excel replaces every occurrence.

Example 3:
Something that you may need to do is replace text with nothing. In that case, use two double quotes (e.g. "") in the 'with this text' section. I frequently need to do this when I import account numbers, that include dashes, from my accounting system but need to use them without the dashes in Excel.

Formula:   =SUBSTITUTE("T-01-02-740-65","-","")
Result:      T010274065

You could also accomplish this with Find & Replace but you would need to do this every time the data is updated. Also, there will be times when you don't want to affect the original cells.

Substitute Function Excel

Example 4:
As with most other things in Excel, you can use cell references to supply information to SUBSTITUTE function/Formula.

In the previous example if your account numbers are in column B starting at row 10, you can enter =SUBSTITUTE(B10,"-","") in cell C10 and copy the formula down the column.

This is just a small taste of how the SUBSTITUTE function/Formula can be used for everyday applications.

Like most of these tips, the more you practice using this, the more ways you will think of to use it.