ינהאַלט
נעכטן אין די מעראַטאַן 30 עקססעל פאַנגקשאַנז אין 30 טעג מיר געוויינט די פֿונקציע ינדעקס (INDEX) to return the value of the cell located at the intersection of the given row and column.
אויף די 25 טאָג פון די מעראַטאַן, מיר וועלן אָפּגעבן די לערנען פון די פֿונקציע פאַרבייַטן (REPLACE), which belongs to the text category. It replaces a certain number of characters in a text string with other text.
אַזוי, לאָזן אונדז נעמען אַ נעענטער קוק אין די אינפֿאָרמאַציע און ביישפילן וועגן די פונקציע פאַרבייַטן (REPLACE), and if you have additional information or examples, please share them in the comments.
Function 25: REPLACE
פונקציע פאַרבייַטן (REPLACE) replaces characters within text based on the specified number of characters and starting position.
How can the REPLACE function be used?
פונקציע פאַרבייַטן (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 פאַרבייַטן (REPLACE) to insert multiple hyphens.
REPLACE Syntax
פונקציע פאַרבייַטן (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)
פונקציע פאַרבייַטן (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 פאַרטרעטער (SUBSTITUTE), which we will look at later in our marathon.
Example 1: Changing the area code in a phone number
ניצן פאַנגקשאַנז פאַרבייַטן (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 פאַרבייַטן (REPLACE), You can use the function געפֿינען (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
פונקציע פאַרבייַטן (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 נול, 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;"-")
