Contents
Duh di maratonê de 30 fonksiyonên Excel di 30 rojan de me fonksiyonê bikar anî BERSVAN (OFFSET) to return a reference, and also saw that it is very similar to a function NAVEROK (INDEX). In addition, we learned that the function BERSVAN (OFFSET) is recalculated whenever the data on the worksheet changes, and NAVEROK (INDEX) only when changing its arguments.
Di roja 27. a maratonê de em ê fonksiyonê bixwînin DIBERDASITIN (SUBSTITUTE). Like the function DIBERDAXISTIN (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 Dîtin/Diberdaxistin (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 DIBERDASITIN (SUBSTITUTE). If you have other information or examples of how to use this feature, please share it in the comments.
Function 27: SUBSTITUTE
Karî DIBERDASITIN (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?
Karî DIBERDASITIN (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
Karî DIBERDASITIN (SUBSTITUTE) has the following syntax:
SUBSTITUTE(text,old_text,new_text,instance_num)
ПОДСТАВИТЬ(текст;стар_текст;нов_текст;номер_вхождения)
- nivîstok (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
- Karî DIBERDASITIN (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 DIBERDAXISTIN (DIBERDAXISTIN).
Example 1: Changing the name of the region in the report title
Karanîna fonksiyonan DIBERDASITIN (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. Nîşan 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 DIBERDASITIN (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.
Karî LEN (DLSTR) in cell C3 counts the number of characters in cell B3. Function DIBERDASITIN (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;" ";""))
Di hucreya D3 de, fonksiyonê DIBERDASITIN (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;" ";"")))