Replace text based on location
=REPLACE(old_text, start_num, num_chars, new_text)| Parameter | Description |
|---|---|
old_text |
The text to replace. |
start_num |
The starting location in the text to search. |
num_chars |
The number of characters to replace. |
new_text |
The text to replace old_text with. |
REPLACE function takes four separate arguments in a generic syntax like this:
=REPLACE(old_text,start_num,num_chars,new_text)The first argument, old_text, is the text string to be processed. The second argument, start_num, specifies the numeric position where replacement sho
=REPLACE("C:\docs",1,1,"D") // returns "D:\docs"
=REPLACE("ABC123",4,3,"456") // returns "ABC456"
=REPLACE("XYZ",1,1,"") // returns "YZ"
=REPLACE("www.google.com",1,4,"") // returns "google.com"In the example below, the goal is to replace the year values in the middle of the text strings in column B with the year 2025. This is a scenario wher
=REPLACE(B5,5,4,"2025")In the worksheet below, the goal is to change the first letter of each path in column B to the letter "Z". This is another good use case for the REPLA
=REPLACE(B5,1,1,"Z")The REPLACE function can be used to remove text by providing an empty string ("") for the new_text argument. In the example below, the goal is to stri
=REPLACE(B5,1,4,"")In the example below, we have the same problem as above, except the www isn't always present. This means we need to check for the presence of these ch
=IF(LEFT(B5,4)="www.",REPLACE(B5,1,4,""),B5)