The REPLACE is a Text function. But you can replace part of a text, number or even a date with the Replace function in Google Sheets.
I have included enough formula examples for you to master this simple Text function.
Here is the Syntax:
REPLACE(text, position, length, new_text)
I don’t think I should explain about this function’s arguments. The example section below can shed light on that.
Formula Examples to the REPLACE Function in Google Sheets
Let’s begin with text replacing. Then you can learn how to replace part of numbers and dates.
How to Replace Part of Text Using the Replace Function in Google Sheets?
For the examples below, I am using the text in Cell A1 which is “Info Inspired Blog”.
1. The Replace Formula to Change the Last Part of the Text
This formula replaces the text “Blog” with “Website”.
=REPLACE(A1,15,4,"Website")
You can also use the text within the formula as below.
=REPLACE("Info Inspired Blog",15,4,"Website")
2. The Replace Formula to Change the Beginning of the Text
I am replacing the word “Info” at the beginning with the Replace function. You may please note that here I am just leaving the replace text as blank.
=REPLACE(A1,1,5,"")
Result: Inspired Blog
That means you can use Google Sheets Replace function to remove part of a string.
3. The Formula to Change Any Part of a Text
I am removing the text “inspired” which is in the middle of the text in A1.
=REPLACE(A1,5,9,"")
Result: Info Blog
4. Replace Texts in an Array Using the Replace Function in Google Sheets
See how I am replacing part of item codes in Column A with the Replace Array Function.
How to Replace Part of a Number Using the Function Replace in Google Sheets?
Normally such replacement is not required. But I just want to tell you that you can replace part of any number too using the Replace function.
For this example, the value in cell A1 is 1555.
Here is the formula:
=REPLACE(A1,2,3,444)
In the last part, i.e. in the replace text, you can use/do not use the double quotes. It won’t affect the result.
Result: 1444
It preserves the number format too. But I recommend you to wrap this formula with the Value function as below.
=VALUE(REPLACE(A1,2,3,444))
How to Replace Part of a Date Using the Replace Function in Google Sheets?
You can replace, day, month, and year using the function Replace in Google Sheets. See the screenshot for formulas.
Similar to the number, this formula preserves the date formats. But when you use this formula output in other calculations, wrap the formula with the VALUE function as above.
Related:
1. Google Sheets Regexreplace Function How to and Examples
2. How to Use SUBSTITUTE Function in Google Sheets and Its Practical Use
