Jawadul Islam Chowdhury
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.
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.
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)
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.
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")
- 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.
- Similarly to replace the beginning string from the text in cell B6, insert the following formula in C6.
=REPLACE(B6,1,6,"Monthly")
- Hence, the formula will replace the initial text string Annual which is 6 characters long from the text position 1 with the text Monthly.
- 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")
- The formula will replace the first text string Monthly which is 7 characters long from the text position 1 with the text Yearly.
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")
- 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.
- Similarly to replace the ending string from the text in cell B6, insert the following formula in C6.
=REPLACE(B6,14,8,"budget")
- 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.
- 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")
- Consequently, the formula will replace the ending text string expense which is 8 characters long from the text position 14 with the text cost.
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")
- 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.
- Similarly to replace any string from the text in cell B6, insert the following formula in C6.
=REPLACE(B6,8,5,"revenue")
- 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.
- 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")
- 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.
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"))
- 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.
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))
- 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.
- 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.
- 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.
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)
- 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.
- 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.
- 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.
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.
