Replaces a string of characters, starting at a given position and ending a given number of characters beyond
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
Sample usage
REPLACE("Hello Dorld!", 7, 1, "W")
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?
If you have an Enterprise plan, you can use AI to help with formula creation.
Use the Formula Handbook template to find more support resources, and view 100+ formulas, including a glossary of every function that you can practice working with in real time, and examples of commonly used and advanced formulas.
Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.