Mataupu
O ananafi i le malafoni 30 Excel galue i le 30 aso sa matou faaaogaina le galuega INDEX (INDEX) to return the value of the cell located at the intersection of the given row and column.
I le aso 25 o le malafoni, o le a matou tuʻuina atu le suʻesuʻega o le gaioiga SILI (REPLACE), which belongs to the text category. It replaces a certain number of characters in a text string with other text.
O lea la, seʻi o tatou vaʻavaʻai totoʻa i faʻamatalaga ma faʻataʻitaʻiga i luga o le galuega SILI (REPLACE), and if you have additional information or examples, please share them in the comments.
Function 25: REPLACE
galuega tauave SILI (REPLACE) replaces characters within text based on the specified number of characters and starting position.
How can the REPLACE function be used?
galuega tauave SILI (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 SILI (REPLACE) to insert multiple hyphens.
REPLACE Syntax
galuega tauave SILI (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)
galuega tauave SILI (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 SUI FUA (SUBSTITUTE), which we will look at later in our marathon.
Example 1: Changing the area code in a phone number
Fa'aaogaina galuega SILI (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 SILI (REPLACE), You can use the function MAUA (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
galuega tauave SILI (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 o, 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;"-")
