30 Excel Functions in 30 Days: 27 – SUBSTITUTE

Icon30DayYesterday, in the 30XL30D challenge, we used the OFFSET function to return a reference, and saw that it is similar to the non-volatile INDEX function.

SUBSTITUTE function

For day 27 in the challenge, we’ll examine the SUBSTITUTE function. Like the REPLACE function, it replaces old text with new text, in a text string, but can replace multiple instances of the same text.

In some situations though, it’s quicker and easier to use the Find/Replace command on the Excel Ribbon, with Match Case option turned on, for case sensitive replacement.

So, let’s take a look at the SUBSTITUTE information and examples, and if you have other tips or examples, please share them in the comments.

Function 27: SUBSTITUTE

The SUBSTITUTE function replaces old text with new text, in a text string. It will replace all instances of the old text, unless a specific occurrence is selected, and SUBSTITUTE is case sensitive.

Substitute00

How Could You Use SUBSTITUTE?

The SUBSTITUTE function replaces old text with new text, in a text string, so you could use it to:

  • Change region name in report title
  • Remove non-printing characters
  • Replace last space character

SUBSTITUTE Syntax

The SUBSTITUTE function has the following syntax:

  • SUBSTITUTE(text,old_text,new_text,instance_num)
    • text is the text string or cell reference, where text will be replaced.
    • old_text is the text that will be removed
    • new_text is the text that will be added
    • instance_number is the specific occurrence of old text that you want replaced

SUBSTITUTE Traps

  • The SUBSTITUTE function can replace all instances of the old text, so use the instance_num argument if you want only a specific occurrence of old text replaced.
  • For replacements that are not case sensitive, you can use the REPLACE function.

Example 1: Change region name in report title

With the SUBSTITUTE function, you can create a report title that changes automatically, based on the region name that is selected.

In this example, the report title is entered in cell C11, which is named RptTitle. The “yyy” in the title text will be replaced with the region name, selected in cell D13.

=SUBSTITUTE(RptTitle,”yyy”,D13)

Change region name with SUBSTITUTE function
Change region name with SUBSTITUTE function

Example 2: Remove non-printing characters

When you copy data from a website, there might be hidden, non-printing space characters in the text.

If you try to remove space characters from the text in Excel, the TRIM function can’t remove them. The characters aren’t normal space characters (character 32); they are non-breaking space characters (character 160).

Instead, you can use the SUBSTITUTE function to replace each of the non-printing spaces with a normal space character. Then, use TRIM to remove all the extra spaces.

=TRIM(SUBSTITUTE(B3,CHAR(160),” “))

Substitute02

Example 3: Replace last space character

Instead of replacing all instances of a text string, you can use the SUBSTITUTE function’s instance_number argument to select a specific instance. In this list of recipe ingredients, we want to replace the last space character only.

In cell C3, the LEN function calculates the number of characters in cell B3. The SUBSTITUTE function replaces all the space characters with empty strings, and the second LEN function finds the length of the revised string. The length is 2 characters shorter, so there are two spaces.

=LEN(B3)-LEN(SUBSTITUTE(B3,” “,””))

Substitute03a

In cell D3, the SUBSTITUTE function replaces only the 2nd space character with the new text – ” | ”

=SUBSTITUTE(B3,” “,” | “,C3)

Instead of using two columns for this formula, you could combine them into one long formula.

=SUBSTITUTE(B3,” “,” | “,LEN(B3)-LEN(SUBSTITUTE(B3,” “,””)))

Substitute03b

Download the SUBSTITUTE Function File

To see the formulas used in today’s examples, you can download the SUBSTITUTE function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the SUBSTITUTE Video

To see a demonstration of the examples in the SUBSTITUTE function sample workbook, you can watch this short Excel video tutorial.

_____________

7 thoughts on “30 Excel Functions in 30 Days: 27 – SUBSTITUTE”

  1. I’ve used it to count the number of items in a comma separated list.

    In cell A1 1,2,3,4,5,6

    =LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(44),””))+1

    Jesse

  2. As outlined on a prior post SUBSTITUTE combined with REPT can be handy to retrieve a specific sub section of a string

    A1: “The Cow Jumped Over the Moon”

    B1: 3

    C1:

    =TRIM(MID(SUBSTITUTE(A1,” “,REPT(” “,LEN(A1))),1+LEN(A1)*(B1-1),LEN(A1)))

    the above would return “Jumped” – the 3rd sub string (delimited by space) – altering B1 alters C1 accordingly.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.