Садржај
Јуче на маратону 30 Екцел функција за 30 дана we used the function ИНДЕКС (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.
So, let’s take a closer look at the information and examples on the function РЕПЛАЦЕ (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) – text string in which the characters will be replaced.
- старт_нум (start_pos) – start position of old characters.
- број_знакова (num_chars) – the number of old characters.
- нови_текст (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;"-")
