This guide covers everything you need to know about the Google Sheets REPLACE function, including its definition, syntax, use cases, and how to use it.
What is the REPLACE Function? How Does It Work?
The REPLACE function in Google Sheets gives you a new piece of text by taking an original text and swapping out a specific part of it with different text. For example, if you have the sentence “I love cats” and you want to change “cats” to “dogs,” the REPLACE function can make it “I love dogs.”
The primary purpose of the REPLACE function is to help users change a certain part of a text string without altering the entire string. This can be extremely useful in data cleaning tasks and updating specific parts of your text data.
How does it work? The function works by scanning through a given text string from a specified position. It then replaces a certain length of characters from that position with new text. The user determines the position and length. The function begins counting from 1, so if you input 1 as the position, the REPLACE function will start from the first character of your text string.
The output of the REPLACE function is always a text string, regardless of whether your original data or the replacement text is a number. Consider combining the REPLACE function with the VALUE function if you need a numeric output.
It’s important to note that the REPLACE function is case-sensitive and only replaces exact matches. Its siblings, REGEXREPLACE and SUBSTITUTE, offer more flexible and advanced replacement options and might be more suitable for complex tasks and patterns.
REPLACE Syntax
The syntax and arguments for the REPLACE function in Google Sheets are as follows:
REPLACE(text, position, length, new_text)
Here’s a breakdown of these arguments:
- text: This is the original text string in which you want to replace a part. This could be a direct input within quotes or a cell reference containing the text.
- position: This is the position in the text string where you want the replacement to start. The position count begins from 1, which means if you input 1, the replacement will start from the first character of your text string.
- length: This refers to the number of characters you want to replace in the original text. The count starts from the position number you’ve specified.
- new_text: This is the text you want to insert in place of the part of the original text you’re replacing.
When using this function, there are a few additional points regarding the syntax and arguments you should keep in mind:
- If the position number is greater than the length of the text, the REPLACE function will return an error.
- If the length argument is greater than the number of characters remaining in the text (from the specified position), the REPLACE function will replace all characters from the position to the end of the text.
- The REPLACE function returns a text string as output. If you want to convert this output into a number, you can use the VALUE function in conjunction with the REPLACE function. For example, VALUE(REPLACE(…)).
- If the new_text argument is an empty string (“”), the REPLACE function will simply remove the specified characters from the original text.
- The REPLACE function can be used with other functions like FIND or SEARCH to dynamically determine the position and length arguments.
Examples of How to Use the REPLACE Function
Here are some practical examples that illustrate how to use the REPLACE function in Google Sheets:
Example #1: Replacing Text in a String
Let’s say you have a cell (A1) with the text “Hello, World!” and you want to replace the word “World” with “Google Sheets”. You can use the REPLACE function to do this. The syntax would look like this:
=REPLACE(A1, 7, 5, “Google Sheets”)
In this example, the old_text is the text in cell A1, the start_num is 7 (the starting position of the word “World”), the num_chars is 5 (the number of characters in the word “World”), and the new_text is “Google Sheets”. The result in the cell where you entered the formula would be “Hello, Google Sheets!”.
Example #2: Replacing Numbers in a String
Suppose you have a cell (B1) with the text “My phone number is 1234567890” and want to replace the number with a new one. Here’s how you would do it:
=REPLACE(B1, 19, 10, “0987654321”)
In this example, the old_text is the text in cell B1, the start_num is 19 (the starting position of the phone number), the num_chars is 10 (the number of characters in the phone number), and the new_text is the new phone number. The result in the cell where you entered the formula would be “My phone number is 0987654321”.
Example #3: Replacing Part of a Date
If you have a date in a cell (C1) in the format “DD/MM/YYYY” and you want to replace the year with a new one, you can use the REPLACE function. Here’s an example:
=REPLACE(C1, 7, 4, “2022”)
In this example, the old_text is the date in cell C1, the start_num is 7 (the starting position of the year in the date), the num_chars is 4 (the number of characters in the year), and the new_text is the new year. The result in the cell where you entered the formula would be the same date but with the year changed to 2022.
Example #4: Replacing Characters in a String
Let’s say you have a cell (D1) with the text “abcdefg” and you want to replace the second and third characters with “XY”. Here’s how you would do it:
=REPLACE(D1, 2, 2, “XY”)
In this example, the old_text is the text in cell D1, the start_num is 2 (the starting position of the characters to be replaced), the num_chars is 2 (the number of characters to be replaced), and the new_text is “XY”. The result in the cell where you entered the formula would be “aXYdefg”.
Why Is REPLACE Not Working? Troubleshooting Common Errors
If you’re having trouble with the REPLACE function in Google Sheets, it might be due to common errors. It’s important to understand these errors, what causes them, and how to fix them.
#VALUE! Error
Cause: This error usually occurs when the function is not properly formatted or the arguments provided are not valid. For instance, if the old_text or new_text arguments are not provided as strings or if the start_num or num_chars arguments are not numbers.
Solution: To fix this error, ensure all the arguments in the REPLACE function are correctly formatted. The old_text and new_text arguments should be strings (text enclosed in quotes), while the start_num and num_chars arguments should be numbers.
#REF! Error
Cause: This error occurs when the cell reference provided in the function is not valid. For example, if the cell reference is not on the current sheet, the #REF! error will be displayed.
Solution: Double-check the cell reference in your REPLACE function. Make sure it exists and that you’ve spelled it correctly.
#N/A Error
Cause: This error is displayed when the Google Sheets can’t find the value you’re searching for. If the old_text argument in the REPLACE function does not exist in the cell you’re referencing, you’ll see the #N/A error.
Solution: Make sure the old_text value exists in the cell you’re referencing. If it doesn’t exist, the REPLACE function won’t be able to find it and replace it with the new_text value.
#NUM! Error
Cause: This error is displayed when the start_num or num_chars arguments in the REPLACE function are negative or larger than the length of the old_text argument.
Solution: Ensure the start_num argument is not negative and does not exceed the length of old_text. Similarly, the num_chars argument should also not be negative or larger than the length of the old_text.
Error: Result of function REPLACE exceeds the cell limit of 50,000 characters
Cause: This error occurs when the result of the REPLACE function exceeds the maximum cell limit in Google Sheets, which is 50,000 characters.
Solution: To resolve this error, try reducing the amount of text you’re trying to replace or split the text across multiple cells and apply the REPLACE function to each cell individually.
Using REPLACE With Other Google Sheets Functions
Combining the REPLACE function with other Google Sheets functions can unlock additional advanced capabilities and efficiencies. Below are a few examples of how REPLACE can be used in conjunction with other Google Sheets functions:
With CONCATENATE
Usage: The CONCATENATE function is used to join two or more text strings into one text string. When used with REPLACE, you can alter specific portions of the combined text.
Example: Suppose you have two cells A1 and B1. A1 contains “Hello” and B1 contains “World”. You can concatenate these two cells and replace “World” with “Google Sheets” using the following formula: =REPLACE(CONCATENATE(A1,” “,B1), 7, 5, “Google Sheets”). The output of this formula will be “Hello Google Sheets”.
With LOWER
Usage: The LOWER function converts all the text to lowercase. When used with REPLACE, you can replace a specific portion of the text and then convert the entire text to lowercase.
Example: Suppose you have a cell A1 containing “Hello World”. You can replace “World” with “Google” and convert all the text to lowercase using the following formula: =LOWER(REPLACE(A1, 7, 5, “Google”)). The output of this formula will be “hello google”.
With TRIM Function
Usage: The TRIM function removes leading, trailing, and repeated spaces in text. When used with REPLACE, you can replace a specific portion of the text and remove any extra spaces.
Example: Suppose you have a cell A1 containing “Hello World”. You can replace “World” with “Google Sheets” and remove extra spaces using the following formula: =TRIM(REPLACE(A1, 7, 5, “Google Sheets”)). The output of this formula will be “Hello Google Sheets”.
With LEN Function
Usage: The LEN function counts the number of characters in a text string. When used with REPLACE, you can replace a specific portion of the text and then count the number of characters in the new text.
Example: Suppose you have a cell A1 containing “Hello World”. You can replace “World” with “Google Sheets” and count the number of characters using the following formula: =LEN(REPLACE(A1, 7, 5, “Google Sheets”)). The output of this formula will be 18.
For more details on the REPLACE function, check out the official documentation at the Google Docs Editors Help Center.
