The REPLACE function in Google Sheets helps you replace part of a text string, starting from a specified position and for a specified length, with a different text string.

Assume cell A1 contains the string "Apple 24, Mango 50, Total 74". The following formula would return "Apple 24, Mango 50: Total 74":

=REPLACE(A1, 20, 1, ":")

One challenge you may encounter when using this function is determining the position of the string to replace. Let’s explore how to tackle this. But before we dive further, let’s first understand the arguments in this function.

Syntax of the REPLACE Function in Google Sheets

Here is the syntax:

REPLACE(text, position, length, new_text)

• text – The text in which you want to make the replacement.
• position – The position (in number of characters) where the replacement will begin. The first character is numbered 1.
• length – The number of characters in the text to be replaced.
• new_text – The text that will replace the specified characters.

As a side note, the SUBSTITUTE function matches a specific text and replaces all its occurrences in the string. In contrast, the REPLACE function doesn’t search for text; instead, it replaces characters at a specified position and length.

Using the REPLACE Function and Overcoming Challenges

Let’s start with a basic example.

Assume the text in cell A1 is "Apple or Mango", and you want to replace "or" with "and".

To find the position of "or", use the following formula:

=LEN("Apple ") + 1

This formula would return 7.

Now, use the following REPLACE formula to replace "or" with "and":

=REPLACE(A1, 7, 2, "and")

In short, copy the string up to the substring you want to replace, calculate its length, and add 1. This will give you the starting position for the replacement.

REPLACE Function with Numbers and Dates

The REPLACE function can also handle numbers and dates, but the output will be text. You can convert the result back to a number or date using additional functions.

Example 1: Replacing Part of a Date

If the value in cell A1 is the date 25/12/2024, you can replace 2024 with 2025 using this formula:

=REPLACE(A1, 7, 4, "2025")

The result will be text, but you can format it back into a date using DATEVALUE and TO_DATE in combination with REPLACE:

=TO_DATE(DATEVALUE(REPLACE(A1, 7, 4, "2025")))

Example 2: Replacing Part of a Number

If the value in cell A1 is the number 1250 and you want to change it to 1240, use the following formula:

=VALUE(REPLACE(A1, 3, 1, 4))

Here, the VALUE function is used to convert the text result back to a number.

Resources

• Google Sheets REGEXREPLACE Function: How To and Examples
• Replace Multiple Comma-Separated Values in Google Sheets
• Regex to Replace the Last Occurrence of a Character in Google Sheets
• Extract, Replace, Match the Nth Occurrence of a String or Number in Google Sheets
• How to Replace Every Nth Delimiter in Google Sheets
• Remove or Replace the Last Character from a String in Google Sheets