Replaces a string of characters, starting at a given position and ending a given number of characters beyond.
Sample Usage
REPLACE("Hello Dorld!", 7, 1, "W")
Syntax
REPLACE(
-
text
-
start_position
-
num_chars
-
new_text
- text—The text to replace.
- start_position—From the left, the character starting position (including spaces) that you want to replace.
- num_chars—The number of characters to replace.
- new_text—The replacement text.
Examples
This example references the following sheet information:
| Clothing Item | Item Number | Sold Date | Ship Date | |
|---|---|---|---|---|
| 1 | T-Shirt | C001 | 02/12/19 1:55 PM | 02/15/19 |
| 2 | Pants | C002 | 02/15/19 10:23 AM | 03/20/19 |
| 3 | Jacket | C003 | 02/20/19 2:45 PM | 02/27/19 |
Given the table above, here are some examples of using REPLACE in a sheet:
| Formula | Description | Result |
| =REPLACE([Item Number]1, 1, 1, [Clothing Item]1 + " ") | Replaces the first character of the string in the “Item Number” column on row 1 with the string in “Clothing Item” column on row 1, then adds a space. | T-Shirt 001 |
| =REPLACE([Item Number]2, 1, 2, "A1") | Replaces the first 2 characters in the string of the “Item Number” column on row 2 with the characters “A1” from the formula. | A102 |
| =REPLACE([Sold Date]@row, 9, 9, " to " + [Ship Date]@row) | Replaces the time in the “Sold Date” column on row 3 with the “Ship Date” value in row 3, then adds the string “ to ” (with spaces) between the dates. | 02/20/19 to 02/27/19 |