REPLACE

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 when replacing text strings.
* This function does not support wildcards (? and *).
* 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.
* The equivalent VBA function is VBA.REPLACE
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.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.

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