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 | 销售日期 | Ship Date | |
|---|---|---|---|---|
| 1 | 中超irt | 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 theItem Numbercolumn with the string in row 1 of theClothing Itemcolumn; then, it adds a space | 中超irt 001 |
| =REPLACE([Item Number]2, 1, 2, "A1") | Replaces the first two characters in the string in row 2 of theItem Numbercolumn with the charactersA1from the formula | A102 |
| =REPLACE([Sold Date]@row, 9, 9, " to " + [Ship Date]@row) | Replaces the time in row 3 of the销售日期column with theShip Datevalue in row 3; then, adds the stringto(with spaces) between the dates | 02/20/19 to 02/27/19 |
Still need help?
Use theFormula Handbook templateto 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.