Yesterday, 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.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
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.
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
The SUBSTITUTE function has the following syntax:
- 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
- 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.
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.
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.
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,” “,””)))
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.
YouTube link: Change Text with Excel SUBSTITUTE Function