SUBSTITUTE Function

Replaces one or more text strings with another text string

Over 2 million + professionals use CFI to learn accounting, financial analysis, modeling and more. Unlock the essentials of corporate finance with our free resources and get an exclusive sneak peek at the first module of each course. Start Free

The SUBSTITUTE function in Excel replaces occurrences of specified text within a string. Its syntax is =SUBSTITUTE(text, old_text, new_text, [instance_num]), where instance_num specifies which occurrence to replace. Unlike the REPLACE function, which targets a position within a string, SUBSTITUTE replaces specified text regardless of its position. It is case-sensitive and useful for cleaning and modifying data imported from external sources.

What is the SUBSTITUTE Function?

The SUBSTITUTE Function[1] was introduced in MS Excel 2007 and is available under Excel 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. This 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) – This is the text or reference to a cell that contains text for which we want to substitute characters.
  2. Old_text (required argument) – The text we wish to replace.
  3. New_text (required argument) – The text we want to replace the old_text with.
  4. Instance_num (optional argument) – 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.

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 CFI resources:

Article Sources

  1. SUBSTITUTE Function

Excel Tutorial

To master the art of Excel, check out CFI’s 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 Excel Course now

to take your career to the next level and move up the ladder!

0 search results for ‘