REPLACE(old_text, start_num, num_chars, new_text)

Returns the text string after replacing characters in a specific location.

old_textThe text string with the characters you want to replace.
start_numThe position of the character in "old_text" that is to be replaced.
num_charsThe number of characters in "old_text" that you want replaced.
new_textThe text you want to replace "old_text" with.

Remarks

* This function is not case sensitive.
* This function does not support wildcards (? or *).
* This function uses the position to replace characters.
* This function should be used when you want to replace text that occurs in a specific location in a text string.
* If "start_num" is left blank, then #VALUE! is returned. See Example 11.
* If "num_chars" is left blank, 0 is used. See Example 9
* You can use the FIND function to return the position of a substring within a larger text string. This is case sensitive.
* You can use the SEARCH function to return the position of a substring within a larger text string. This is not case sensitive.
* You can use the SUBSTITUTE function to return a text string after replacing instances of a substring.
* You can use the REPLACEB function if you have languages that use the double-byte character set.
* The REPLACEB function does not appear in the autocomplete but does exist.
* For the Microsoft documentation refer to support.office.com

 A
1=REPLACE("July Data",1,4,"April") = April Data
2=REPLACE("2 March, 2020",1,1,"1") = 1 March, 2020
3=REPLACE("2 March, 2020",10,1,"1") = 2 March, 1020
4=REPLACE("2 March, 2020",12,1,"1") = 2 March, 2010
5=REPLACE("Better Solutions",1,6,"Awesome") = Awesome Solutions
6=REPLACE("Better Solutions",3,4,"st") = Best Solutions
7=REPLACE("123-456-789",4,1,"") = 123456-789
8=REPLACE("123 456 789",4,1,"-") = 123-456 789
9=REPLACE("July Data",1,,"April") = AprilJuly Data
10=REPLACE("July Data",1,0,"April") = AprilJuly Data
11=REPLACE("July Data",,0,"April") = #VALUE!

1 - What text string would you get if you started at position "1" and replaced the next "4" characters with the text "April" in the text string "July Data".
2 - What text string would you get if you started at position "1" and replaced the next "1" character with the text "1" in the text string "2 March, 2020".
3 - What text string would you get if you started at position "10" and replaced the next "1" character with the text "1" in the text string "2 March, 2020".
4 - What text string would you get if you started at position "12" and replaced the next "1" character with the text "1" in the text string "2 March, 2020".
5 - What text string would you get if you started at position "1" and replaced the next "6" characters with the text "Awesome" in the text string "Better Solutions".
6 - What text string would you get if you started at position "3" and replaced the next "4" characters with the text "st" in the text string "Better Solutions".
7 - What text string would you get if you started at position "4" and replaced the next "1" character with the text "" in the text string "123-456-789".
8 - What text string would you get if you started at position "4" and replaced the next "1" character with the text "-" in the text string "123 456 789".
9 - What text string would you get if you started at position "1" and a blank entry for the "num_chars" with the text "April" in the text string "July Data".
10 - What text string would you get if you started at position "1" and replaced the next "0" characters with the text "April" in the text string "July Data".
11 - If the "start_num" is left blank then #VALUE! is returned.

© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited Top