Contents
Duh di maratonê de 30 fonksiyonên Excel di 30 rojan de we used the function NAVEROK (INDEX) to return the value of the cell located at the intersection of the given row and column.
Di roja 25emîn a maratonê de, em ê lêkolîna fonksiyonê bikin DIBERDAXISTIN (REPLACE), which belongs to the text category. It replaces a certain number of characters in a text string with other text.
Ji ber vê yekê, bila em hûrgulî li agahdarî û nimûneyên li ser fonksiyonê binihêrin DIBERDAXISTIN (REPLACE), and if you have additional information or examples, please share them in the comments.
Function 25: REPLACE
Karî DIBERDAXISTIN (REPLACE) replaces characters within text based on the specified number of characters and starting position.
How can the REPLACE function be used?
Karî DIBERDAXISTIN (REPLACE) can replace characters in a text string, for example:
- Change the area code in the phone number.
- Replace space with colon with space.
- Use nested function DIBERDAXISTIN (REPLACE) to insert multiple hyphens.
REPLACE Syntax
Karî DIBERDAXISTIN (REPLACE) has the following syntax:
REPLACE(old_text,start_num,num_chars,new_text)
ЗАМЕНИТЬ(старый_текст;нач_поз;число_знаков;новый_текст)
- old_text (old_text) – text string in which the characters will be replaced.
- start_num (start_pos) – start position of old characters.
- num_chars (num_chars) – the number of old characters.
- new_text (new_text) – text that will be inserted in place of the old characters.
Traps REPLACE (REPLACE)
Karî DIBERDAXISTIN (REPLACE) replaces a specified number of characters starting at the specified position. To replace a specific line of text anywhere in the original text, you can use the function DIBERDASITIN (SUBSTITUTE), which we will look at later in our marathon.
Example 1: Changing the area code in a phone number
Karanîna fonksiyonan DIBERDAXISTIN (REPLACE) You can change the first three digits of a phone number, for example, when a new area code is set. In our case, the new area code is entered in column C, and the corrected phone numbers are displayed in column D.
=REPLACE(B3,1,3,C3)
=ЗАМЕНИТЬ(B3;1;3;C3)
Example 2: Replace a space with a colon with a space
To define a starting position for a function DIBERDAXISTIN (REPLACE), You can use the function FIND (FIND) to find a specific line of text or character. In this example, we want to replace the first space that occurs in a text string with a colon followed by a space.
=REPLACE(B3,FIND(" ",B3,1),1,": ")
=ЗАМЕНИТЬ(B3;НАЙТИ(" ";B3;1);1;": ")
Example 3: Nested REPLACE functions to insert multiple hyphens
Karî DIBERDAXISTIN (REPLACE) is allowed to nest one inside another, so multiple replacements can be made in the source text. In this example, phone numbers must contain hyphens after the first three characters and after the second. Using sifir, as the number of characters to replace, we will get the result that none of the characters of the phone number will be removed, only 2 hyphens will be added.
=REPLACE(REPLACE(B3,4,0,"-"),8,0,"-")
=ЗАМЕНИТЬ(ЗАМЕНИТЬ(B3;4;0;"-");8;0;"-")
