REPLACE Function

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")
  • 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

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 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.

Ask the Community