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 row 1 of the Item Number column with the string in row 1 of the Clothing Item column; then, it adds a space | T-Shirt 001 |
=REPLACE([Item Number]2, 1, 2, "A1") | Replaces the first two characters in the string in row 2 of the Item Number column with the characters A1 from the formula | A102 |
=REPLACE([Sold Date]@row, 9, 9, " to " + [Ship Date]@row) | Replaces the time in row 3 of the Sold Date column 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 |
Still need help?
Use the Smartsheet Formula Template to view examples and practice working with functions in real-time.
Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.