SUBSTITUTE Excel Formula

Author Zaheer    Category Formulas     Tags ,

A Substitute Function replaces the set of character with another. If syntax is:

= Substitute (text, old_text, new_text, nth_appearance)

  • Text is an original string to use in performing substitution
  • Old_text is known as the present character to replace
  • New_text is identified as the new character to replace the old_text
  • Nth_apperance is the optional argument. It is nth appearance of the old_text that you like to replace. If the argument is deleted, then each occurrence of the old_text would be replaced with the new_text.

Excel Substitute function appears to be the same to Excel Replace Function, however this Substitute Function replaces 1 or more instances of the given text string, while Replace Function replace the text within a specified position of supplied string.


The Excel Substitute Function’s a case sensitive. Thus, when the old_text argument is text string ‘A’, it WON’T replace the occurrence of the lower case text string a.

Common Problem Substitute Function

Use of Excel Substitute Function with the numbers, dates and time

Excel substitute function’s planned for use with the text string and return a text string. Thus, when you try to use substitute function with date, time or number, it will offer you unexpected result.

The solution to the problem in conversion of time, date or number in the text, is utilizing Excel Text to Column tool.

  • Use the mouse to opt for the cells, you like to convert to the text (this shouldn’t span more than 1 column)
  • From Data tab just at the top of the Excel work book, opt for the Text Column option
  • Be sure that all of the Delimited options are selected then click next.
  • Be sure that all of the delimiter option are not selected, and then click next again.
  • You aught to be offered selection of Column data format, and then choose text and then click finish

How to Use Excel Substitute Function?

Excel Substitute function becomes helpful when you have lots of cells containing similar data format although with various values. With Substitute function, entering info is allowable only once, and then you can write formula display variation of those cells through the spreadsheet.

  • Prefer the cell where you like to key in the formula then click it using the mouse. Click the inside function box just under its Excel menu.
  • Type the function inside the format: =Substitute (“text”, “old_text”, “new_text”, “instance_num”). This text cell includes the text that you wish to substitute. ‘Old_text’ is the needed text or content to be substituted and ‘new_text’ is the required text substituting it. ‘Instance_num’ is instance of text you like to replace.
  • Make an example so you can learn how substitute function really works. Click the gray cells in order for the entire spreadsheet to be highlighted. Then click format from its menu. Opt for text.
  • Click the cell B1. Then go to function box then key in =Substitute (A1, “2”, “6”, ‘‘1’’). Click enter key and then the text inside cell B1 will appear November 6, 2008. Edit its formula and then take out 1. All occurrences of 2 would be changed in 4.
  • In function box, key in = (A1, 8, 12). Press the enter key. Year now reads 2011. In this scenario, you don’t require ‘’instance_num’’, since there’s simply one “8” that appears on its cell.
  • Get assistance and some other example of substitute function through clicking ‘help’ on Excel menu. Inside the keyword box, key in substitute work sheet function.

To sum it all, this Substitute function looks like:

SUBSTITUTE (text/ cell, old text, new text)

Then again, if it is useful to you, think about this like PHP’s str_replace or the SQL’s REPLACE.

  • Text refers to the cell reference to URL from the log file data.
  • Old text is for example
  • New text is considered as the ‘’’’

Learn it or hate it, you have to know that Excel probably consumes lions share of the present day as several web analyst and search marketing experts. It is the first or the series designed to uncover and explore a few common uses of excel over laid with lots of internet marketing data task.

So, use SUBSTITUTE when you like to replace particular text in the text string, and make use of Replace if you like to replace any text which occurs in the specific location in text string.

Post comment