How to Use REPLACE Function in Google Sheets (4 Examples)

Google sheets is a handy tool while editing and replacing data from a large dataset. Using the REPLACE function users can easily and quickly replace a lot of data in a second. Using this function users can replace text, numbers, or dates from any location of the string easily. You can combine the REPLACE function with other functions to expand its capabilities. In this article, I will show you how to use REPLACE function in Google Sheets.

overview of the article

The above image is the overview of the article. In the image, we can see that we can replace text with just a few steps from different locations of a string. Stick to the following section to learn the step-by-step procedure.


A Sample of Practice Spreadsheet

Click the below link to copy the practice spreadsheet.


What Is REPLACE Function in Google Sheets?

A REPLACE function is a text function that can be used to replace text, numbers, or values in Google sheets. By using this function, the user can extract the desired portion of a text string and replace it with a new string. This can be particularly useful while working with a large amount of data that needs to be transformed in a specific way.

Syntax

The syntax for the REPLACE function is as follows,

REPLACE(text, position, length, new_text)

syntax for the replace function in Google sheets

Arguments

The arguments present in REPLACE function are as follows,

Arguments Requirement Function
text required indicates the text or from which text to replace
position required denotes the position from which the text will be replaced
length required indicates the position from which the text will be replaced.
new_text required new text string with which the user wants to insert

Output

REPLACE(“XYZABC”,4,3, “XVX”); Here, the function will replace three texts starting from the fourth position of the string XYZABC with XVX. And the result of the formula will become XYZXVX.


4 Ideal Examples to Use REPLACE Function in Google Sheets

In Google Sheets, a user can utilize the REPLACE function to replace text strings from any location of the text. As a result, while working with a large dataset, it is easier to change or replace a text string easily and effectively.

Here, the following image contains the dataset for the article. Using this dataset I will show you how to use REPLACE function to substitute part of text from any location like from the beginning, ending, or any other place of the text strings.

dataset for the article


1. Replacing Beginning String of a Text

For the above case, we have the first text value as the Gross profit Margin. But we want it as the Net profit Margin. Now follow the steps below to accomplish this.

📌 Steps:

  • First select cell C5 and enter the following formula.
=REPLACE(B5,1,5,"Net")

Inserting formula for replacing the beginning of string

  • Here, the function will replace the first string Gross which is 5 characters long with the text Net. 1 attribute means text will be replaced from the 1st position of the text string.
  • The formula will result in the following image.

result of replacing the beginning of text string

  • Similarly to replace the beginning string from the text in cell B6, insert the following formula in C6.
=REPLACE(B6,1,6,"Monthly")

insering formula using replace function

  • Hence, the formula will replace the initial text string Annual which is 6 characters long from the text position 1 with the text Monthly.

result of using the replace function

  • And to substitute the beginning string from the text from cell B7, you can use the following formula in cell C7.
=REPLACE(B7,1,7,"Yearly")

using formula using replace function for replacing text from beginning

  • The formula will replace the first text string Monthly which is 7 characters long from the text position 1 with the text Yearly.

final result of using replace function to replace beginning text in google sheets


2. Substituting End Part of a Text

For the above case, the first text value is Gross profit margin which we want to substitute with the text Gross profit ratio. Similarly, we will change the other text values as well. To replace the end part of a text string, follow the below steps.

📌 Steps:

  • First select cell C5 and enter the following formula.
=REPLACE(B5,14,6,"ratio")

inserting formula for replacing the end part

  • Hence, the function will replace the last string margin of cell B5 which is 6 characters long with the text ratio. 14 attribute means the text replacement will take place from the text position 14.
  • Finally, the output will look like the following image.

result of using the replace function

  • Similarly to replace the ending string from the text in cell B6, insert the following formula in C6.
=REPLACE(B6,14,8,"budget")

using replace function to substitute the end part of a string

  • As a result, the formula will replace the ending text string forecast which is 8 characters long with the text budget. Here, the 14 attribute means the text replacement will occur from the text position 14.

result of using the replace function in google sheets to substitute end part

  • And to substitute the end part of the text string from cell B7, you can use the following formula in cell C7.
=REPLACE(B7,14,8,"cost")

result of using the replace function in google sheets

  • Consequently, the formula will replace the ending text string expense which is 8 characters long from the text position 14 with the text cost.

result of using the replace function in google sheets


3. Changing Any Part of a Text

For our case, the first text value is Gross profit margin, which we want to replace with text Gross turnover margin. To replace any part of a text string, follow the below steps.

📌 Steps:

  • First select cell C5 and enter the following formula.
=REPLACE(B5,7,6,"turnover")

inputting the formula using replace function in google sheets

  • Consequently, the function will replace the string profit of cell B5 which is 6 characters long with the text turnover. In the formula, the 7 attribute means text to be replaced from position 7.
  • The final output will look like the following image.

result of using replace function in google sheets to change any part of text

  • Similarly to replace any string from the text in cell B6, insert the following formula in C6.
=REPLACE(B6,8,5,"revenue")

inputting replace function to replace any part of the text in google sheets

  • As a result, the function will replace the string sales of cell B6 which is 5 characters long with the text revenue. In the formula, the 8 attribute means the text is to be replaced from text position 8.

result after using the replace function in google sheets

  • And to substitute any part of the text string from cell B7, you can use the following formula in cell C7.
=REPLACE(B7,9,4,"operating")

inserting replace function to change any part of the text

  • Consequently, the function will replace the string rent of cell B7 which is 4 characters long with the text operating. In the formula, the 9 attribute means text to be replaced from the text position 9.

Result of using the replace function to change any part of text in Google sheets


4. Replacing Text of an Array

To replace similar texts of an array you can use the combination of the REPLACE function and the ARRAYFORMULA function. This method will become more convenient replacing text over a large amount of time. Follow the below steps.

📌 Steps:

  • First of all select cell C5 and input the following formula.
=ARRAYFORMULA(REPLACE(B5:B9,1,5,"Invoice"))

inserting formula with combination of ARRAYFORMULA and REPLACE function in Google sheets

  • Here in the formula, a combination of the ARRAYFORMULA and REPLACE is used to replace the text Order with the text Invoice from the whole column B at a time.
  • Here, the function will replace the string order of cells B5:B9 which are 5 characters long with the text Invoice. In the formula, the 1 attribute means text to be replaced from the text position 1.
  • Finally, the output of the formula will look like the following image.

result of using the combination of ARRAYFORMULA and REPLACE functions in google sheets


How to Substitute Part of a Number in Google Sheets

Though REPLACE is a text function, users can substitute numbers in Google sheets using the function. In our case, the first number is 1245, which we want to replace with the number 1444. Follow the below steps to accomplish that.

📌 Steps:

  • First of all, select cell C5 and input the following formula.
=VALUE(REPLACE(B5,2,3,444))

inserting replace function to substitute part of number

  • Accordingly, the formula will replace 3 numbers from text position 2 of cell B5 with 444. Here, the VALUE function is used to return the number as a value.

result of using replace function in google sheets to change part of number

  • Similarly, to replace the second number 1245 with 4445, select cell C6 and enter the following formula.
=VALUE(REPLACE(B6,1,3,444))
  • Consequently, the formula will replace 3 numbers from the text position 1 of cell B6 with 444.

inserting replace function to substitute part of number

  • And, to replace the second number 1245 with 1244, select cell C7 and insert the following formula.
=VALUE(REPLACE(B7,3,2,44))
  • As a result, the formula will replace 2 numbers from the text position 3 of cell B7 with 44.

result of using replace function to change part of number


How to Replace Part of Dates in Google Sheets

You can replace parts of dates like day, month, or year using the REPLACE function. For our case, we want to replace the dates in column B, with different days, months, or years.  Follow the steps below.

📌 Steps:

  • First of all to replace the date 02/05/2022 with 15/05/2020 of cell C5, select cell C5 and insert the following formula.
=REPLACE(B5,1,2,15)

insering replace function to repace part of date

  • As a result, the function will replace the date string 02 of cell B5 which is 2 characters long with 15. In the formula, the 1 attribute means text to be replaced from text position 1.

result of replacing day part of date

  • Similarly, to replace the month of cell C6, select the cell and enter the following formula.
=REPLACE(B6,4,2,11)
  • Consequently, the formula will replace date string 05 which is 2 characters long, from the text position 4 of cell B5 with 11.

result of replacing month part of date

  • Again to replace the year of cell C7, select the cell and input the following formula.
=REPLACE(B7,9,2,20)
  • Hence the formula will replace date string 22 which is 2 characters long, from the text position 9 of cell B5 with 20.

result of using replacing the year part of date


Things to Remember

The REPLACE function is case-sensitive, so you need to be mindful of the case of the original text and the text you want to replace it with.


Conclusion

In conclusion, we can say that the REPLACE function is a useful function that can be used for replacing text as well as numbers and dates. We can replace text, numbers, or dates from different locations like from the beginning or end of a string, and from any other locations as well. You can use the ARRAYFORMULA along with the REPLACE function to substitute similar strings from a range of data. Following the article hopefully, you can now use the REPLACE function as you need. Comment below with your queries and suggestion regarding the article. Visit Officewheel for more Google sheets-related helpful articles.

Jawadul Islam Chowdhury

Jawadul Islam Chowdhury

Hello! This is Jawad. I create Google Spreadsheets-related content for OfficeWheel. I enjoy doing research and solving Google Spreadsheet-related problems. I love to learn new things and teach them to others.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo