Contents
Yesterday in the marathon 30 Excel functions in 30 days we used the function INDEX (INDEX) to return the value of the cell located at the intersection of the given row and column.
On the 25th day of the marathon, we will devote the study of the function REPLACE (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 (REPLACE), and if you have additional information or examples, please share them in the comments.
Function 25: REPLACE
Function REPLACE (REPLACE) replaces characters within text based on the specified number of characters and starting position.
How can the REPLACE function be used?
Function REPLACE (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 (REPLACE) to insert multiple hyphens.
REPLACE Syntax
Function REPLACE (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)
Function 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 (SUBSTITUTE), which we will look at later in our marathon.
Example 1: Changing the area code in a phone number
Using functions REPLACE (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 (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
Function REPLACE (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 zero, 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;"-")
