Close the Skill Gap -> Enroll to be a Certified Financial Modeling & Valuation Analyst (FMVA)® Today!

SUBSTITUTE Function

Replaces one or more text strings with another text string

What is the SUBSTITUTE Function?

The SUBSTITUTE Function was introduced in MS Excel 2007 and is available under Text functions. It will replace one or more text strings with another text string. The function is useful when we wish to substitute old text in a string with a new string.

In financial analysis, we often import data from external sources such as PDFs. The function helps to substitute text strings from imported data. For example, if the data imported is 967-098-908, we can use this function to transform it into 967098908.

 

Difference between SUBSTITUTE and REPLACE Functions

The SUBSTITUTE function is quite similar to the REPLACE function. However, SUBSTITUTE replaces one or more instances of a given text string whereas REPLACE replaces text in a specified position of a supplied string.

 

Formula

=SUBSTITUTE(text, old_text, new_text, [instance_num])

 

The SUBSTITUTE function uses the following arguments:

  1. Text (required argument) – It is the text or reference to a cell that contains text for which we want to substitute characters.
  2. Old_text (required argument) – It is the text we wish to replace.
  3. New_text (required argument) – It is the text we want to replace the old_text with.
  4. Instance_num (optional argument) – It will specify 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.

 

How to use the SUBSTITUTE Function in Excel?

To understand the uses of the SUBSTITUTE function, let us consider a few examples:

 

Example 1

Suppose we are given the following data:

 

SUBSTITUTE Function

 

We used the formulas below to replace text in the example above.

 

SUBSTITUTE Function - Example 1a

 

Instance limits SUBSTITUTE replacement to one particular instance of old_text. If instance is not supplied, all instances of old_text are replaced with new_text. We get the results below:

 

SUBSTITUTE Function - Example 1a

 

Also, SUBSTITUTE is case-sensitive and does not support wildcards. In the example above, if for Lime we provide old text as “l” then no replacement will be done by the function.

 

Example 2

 If we want to convert a column number to an Excel column letter (e.g. A, B, C, etc.), we can use a formula based on the ADDRESS and SUBSTITUTE functions. Suppose we are given the data below:

 

SUBSTITUTE Function - Example 2

 

The formula used is:

 

SUBSTITUTE Function - Example 2a

 

We get the results below:

 

SUBSTITUTE Function - Example 2b

 

The first step was to construct an address that would contain the column number. We did it using the ADDRESS function by providing 1 for row number, a column number from A1, and 4 for the abs_num argument (to get a relative reference): ADDRESS(1,B5,4).

With the information, ADDRESS returns the text “A1.” We then used SUBSTITUTE to strip remove the number 1 like this: =SUBSTITUTE(“A1″,”1″,””).

 

Click here to download the sample Excel file

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your valuation modeling analysis. To learn more, check out these additional resources:

  • Advanced Excel Tutorial
  • Advanced Excel Formulas in Excel
  • Excel Shortcuts List
  • Financial Modeling Analyst Designation

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!