Have you ever wondered how to quickly replace part of a text, number, or even a date in Google Sheets? Look no further! The REPLACE function is here to save the day.
Mastering the REPLACE Function: Simple Formula Examples
Let’s dive right into some formula examples to help you understand how to use the REPLACE function effectively.
Replacing Text
Let’s start with text replacement examples. Imagine we have a cell (A1) that contains the text « Info Inspired Blog. » Here’s how you can replace part of the text:
-
Changing the Last Part of the Text:
- Formula:
=REPLACE(A1,15,4,"Website") - Result: « Info Inspired Website »
- Formula:
-
Changing the Beginning of the Text:
- Formula:
=REPLACE(A1,1,5,"") - Result: « Inspired Blog »
- Formula:
-
Changing Any Part of the Text:
- Formula:
=REPLACE(A1,5,9,"") - Result: « Info Blog »
- Formula:
Replacing Texts in an Array
If you have a column of item codes that need to be modified, the REPLACE function can help. Check out this example:
Replacing Part of a Number
Although it might not be commonly required, the REPLACE function can also be used to replace a part of a number. Let’s say we have the number 1555 in cell A1:
- Formula:
=REPLACE(A1,2,3,444) - Result: 1444
To preserve the number format, it’s recommended to wrap the formula with the VALUE function, like this: =VALUE(REPLACE(A1,2,3,444)).
Replacing Part of a Date
Yes, you read it right! You can even replace parts of a date using the REPLACE function in Google Sheets. Whether it’s the day, month, or year, this function has got you covered. Check out the formulas in the screenshot:
Remember to wrap the formula with the VALUE function when using the output in other calculations.
And that’s it! With these examples, you should now have a solid understanding of how to utilize the powerful REPLACE function in Google Sheets.
Stay tuned for more exciting Google Sheets tips and tricks!
—
Related articles:
