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