Contents
Yesterday in the marathon 30 Excel functions in 30 days we used the function OFFSET (OFFSET) to return a reference, and also saw that it is very similar to a function INDEX (INDEX). In addition, we learned that the function OFFSET (OFFSET) is recalculated whenever the data on the worksheet changes, and INDEX (INDEX) only when changing its arguments.
On the 27th day of the marathon, we will study the function SUBSTITUTE (SUBSTITUTE). Like the function REPLACE (REPLACE), it replaces the old text with the new one, and can also make multiple replacements for the same text in a string.
As practice shows, in some situations it is faster and easier to use commands Find/Replace (Find/Replace) when it is necessary to make the replacement case sensitive.
So, let’s take a closer look at the information and examples on SUBSTITUTE (SUBSTITUTE). If you have other information or examples of how to use this feature, please share it in the comments.
Function 27: SUBSTITUTE
Function SUBSTITUTE (SUBSTITUTE) replaces old text with new text within a text string. The function will replace all repetitions of the old text until a certain condition is met. It is case sensitive.
How can you use the SUBSTITUTE function?
Function SUBSTITUTE (SUBSTITUTE) replaces old text with new text within a text string. You can use it to:
- Change the name of the region in the report header.
- Remove non-printable characters.
- Replace last space character.
SUBSTITUTE Syntax
Function SUBSTITUTE (SUBSTITUTE) has the following syntax:
SUBSTITUTE(text,old_text,new_text,instance_num)
ПОДСТАВИТЬ(текст;стар_текст;нов_текст;номер_вхождения)
- text (text) – text string or link where the text will be replaced.
- old_text (old_text) – text to be replaced.
- new_text (new_text) – text to be inserted.
- instance_num (entry_number) is the number of the occurrence of the text to be replaced (optional).
SUBSTITUTE Traps
- Function SUBSTITUTE (SUBSTITUTE) can replace all repetitions of the old text, so if you only need to replace a specific occurrence, use the argument instance_num (entry_number).
- If you need to do a case-insensitive replacement, use the function REPLACE (REPLACE).
Example 1: Changing the name of the region in the report title
Using functions SUBSTITUTE (SUBSTITUTE) You can create a report title that automatically changes depending on which region is selected. In this example, the report title is entered in cell C11, which is named RptTitle. Symbol yyy in the heading text will be replaced with the name of the region selected in cell D13.
=SUBSTITUTE(RptTitle,"yyy",D13)
=ПОДСТАВИТЬ(RptTitle;"yyy";D13)
Example 2: Remove non-printing characters
When copying data from a website, extra space characters may appear in the text. The text can contain both regular spaces (character 32) and non-breaking spaces (character 160). When you try to delete them, you will find that the function TRIM (TRIM) is unable to remove non-breaking spaces.
Luckily, you can use the function SUBSTITUTE (SUBSTITUTE) to replace each non-breaking space with a normal one, and then using the function TRIM (TRIM), remove all extra spaces.
=TRIM(SUBSTITUTE(B3,CHAR(160)," "))
=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(B3;СИМВОЛ(160);" "))
Example 3: Replacing the last space character
To avoid replacing all occurrences of a text string, you can use the argument instance_num (entry_number) to indicate which occurrence to replace. The following example is an ingredient list for a recipe where only the last space character needs to be replaced.
Function LEN (DLSTR) in cell C3 counts the number of characters in cell B3. Function SUBSTITUTE (SUBSTITUTE) replaces all whitespace characters with an empty string, and the second function LEN (DLSTR) finds the length of the processed string. The length is 2 characters shorter, which means there were 2 spaces in the string.
=LEN(B3)-LEN(SUBSTITUTE(B3," ",""))
=ДЛСТР(B3)-ДЛСТР(ПОДСТАВИТЬ(B3;" ";""))
In cell D3, the function SUBSTITUTE (SUBSTITUTE) replaces the second space character with a new string » | “.
=SUBSTITUTE(B3," "," | ",C3)
=ПОДСТАВИТЬ(B3;" ";" | ";C3)
In order not to use two formulas to solve this problem, you can combine them into one long one:
=SUBSTITUTE(B3," "," | ",LEN(B3)-LEN(SUBSTITUTE(B3," ","")))
=ПОДСТАВИТЬ(B3;" ";" | ";ДЛСТР(B3)-ДЛСТР(ПОДСТАВИТЬ(B3;" ";"")))