What Is REPLACE In Google Sheets?
The REPLACE in Google Sheets replaces a part of data such as, text, number, character, string, etc., from the selected existing data and replaces it with a new string. The Google Sheets REPLACE function requires numeric, alpha-numeric or textual data as an input and can be replace by any data. For example, we have an alpha-numeric data given below. Let us retain part of the text and replace the other part with a new value using the REPLACE in Google Sheets.
Select cell B2, enter the formula =REPLACE(A2,4,3,888), press “Enter” and drag the formula from cell B2 to B3 using the fill handle, as shown below.
The output is shown above. The cells have retained the alphabet part and replaced the numeric part with the number 888. We were able to drag the formula because the input strings lengths are same.
Key Takeaways
- The REPLACE in Google Sheets is a Text function thathelps users replace an old text character, word, or an entire string with a new set of characters or strings.
- We can give the new_text as a cell value entered within double-quotes or as a cell reference by using a helper column or a new column. This method helps us use a single formula and drag it to the rest of the result cells. It saves times and we can avoid manual entering of all the cells, as we learnt in Example 2 and 4.
- The second parameterand the third parameter, cannot have a non-numeric or a negative value. Especially the 2nd cannot have the value as 0.
- In a large dataset, if we want to combine with 2 or more columns data, we can use the CONCATENATE function along with the REPLACE function to combine values in the right format.
Syntax
The syntax of the REPLACE formula in Google Sheets is,
The mandatory arguments of the REPLACE formula in Google Sheets are,
- text: It is the selected data that can be a text, number, alpha-numeric value, etc, to be replaced.
- position: It is the starting position of the original string from where the replacement should begin.
- length: It is a numeric value and indicates the number of characters to be replaced.
- new_text: It is the new data that can be a text, number, alpha-numeric value, etc, to replace the existing original data.
How To Use REPLACE Function In Google Sheets?
We can use the REPLACE Function In Google Sheets in two ways, as follows:
- Access from the Google Sheets ribbon.
- Enter the formula in the worksheet manually.
Method #1 – Access from the Google Sheets ribbon
Step 1: Choose an empty cell for the output – select the “Insert” tab – click the “Function” option right arrow – click the “Text” option right arrow – select the “REPLACE” function, as shown below.
Step 2: The “REPLACE” formulaappears, as shown below. Enter the argument as cell reference.
Method #2 – Enter the formula in the worksheet manually
Step 1: Select an empty cell for the output.
Step 2: Type =REPLACE( in the cell, as shown below. [Alternatively, type =R or =RE and double-click the REPLACE from the Google Sheets suggestions.]
Step 3: Enter the arguments as cell values or cell references and close the brackets.
Step 4: Press Enter to view the outcome.
Examples
We will consider specific REPLACE In Google Sheets examples to replace an entire text, numeric values, only characters in a text, etc. Let us combine the REPLACE and the CONCATENATE functions too.
Example #1 – Replace Text in a String
The data given below consists of two email ids with different domain names. We will use the REPLACE function to change the domain name as the text to a different one.
The steps to Replace Text in a String are,
Step 1: Select cell B2 and enter the formula =REPLACE(A2,8,13,”wallstreetmojo”), as shown below.
Step 2: Press “Enter”. Google Sheets provides the Auto fill option, as shown below. We can either choose that or use the drag using the fill handle method.
Step 3: Select the tick mark and the results will get auto filled as shown below.
The output is shown above with the email id domain names changed to wallstreetmojo. It is so because the starting position is given as 8th and the length of the characters is given as 13, i.e., the length of the text “wallstreetmojo”.
Example #2 – Replace Numbers in a String
Consider the numeric data given below where the values are numbers and we will replace it to their respective number names using the Google Sheets REPLACE function. However, instead of entering the argument values as cell values within double-quotes like the previous example, we have added an extra column for the new-text and will provide their cell reference.
The steps to Replace Numbers in a String are,
Step 1: Select cell C2 and enter the formula =REPLACE(B2,1,10,A2), as shown below.
Step 2: Press “Enter” and drag the formula from cell C2 to C6 using the fill handle to get the following results.
We see that the Columns A and C are the same, as it is the new_text replacing the entire text, i.e., the numeric value to its number names. Here, to be on the safer side we have provided the length as 10, to avoid deleting or overwriting on any other value.
Example #3 – Replace Characters in a String
In the dataset given below, we will replace a character in the text and change it to a new value using the using the Google Sheets REPLACE function.
The steps to Replace Characters in a String are,
Step 1: Select cell B2, enter the formula =REPLACE(A2,1,1,”T”) and press “Enter”, as shown below.
Step 2: Drag the formula from cell B2 to B6 using the fill handle to get the following results.
Example #4 – Combine REPLACE with CONCATENATE
The data given below consists of the first names and the last name of employees. We will use the Replace and the Concatenate functions to get the names in the right format.
The steps to Combine REPLACE with CONCATENATE are,
Step 1: Select cell C2 and enter the formula =CONCATENATE(REPLACE(A2,1,1,C2),B2), as shown below.
Step 2: Press “Enter”. We get the result shown below where we see that the first names first letter is replaced to capital letter and then the CONCATENATE function combines both the first and the last name and returns the final result of the name in the right format.
Step 3: Finally, drag the formula from cell D2 to D7 using the fill handle, to get the following results.
In cell D2, the result is “Jonny Walker” and we see that after the first name we have a space which is because in Column A we have added a space after the names. If not, the result will be “JonnyWalker” without space. It’s the same for the rest of the cells.
Example #5 – Remove a String –
We will consider the following dataset with a value of a place with its city state and country. Let us remove the state and replace it with blank using the REPLACE formula.
The procedure to Remove a string is,
Select cell B2, enter the formula =REPLACE(A2,12,11,””) and press “Enter”, as shown below.
In this example, Karnataka starts from the 12th position. Therefore, we have replaced the 12th position with “”, i.e., a blank space till 11 characters length, indicating that the text is removed from 12th position. Then, we get the output shown above.
Important Things To Note
- We get the #VALUE! error, when either of the second, third, or fourth argument value is not provided.
- If the 2nd argument is 0, then we will get an error. The position argument value must always be greater than 0 as there is no 0 position.
- If the proper cell value or cell reference is not selected, we get the “#NAME?” error.
Frequently Asked Questions (FAQs)
The Google Sheets REPLACE isn’t working because of the following reasons:
a. The argument values or cell reference are not organized properly.
b. The cell range selected is modified or deleted.
c. The cell value for the arguments given directly are not inserted within double-quotes.
d. The argument values are not provided in the right format.
e. There is a mismatch in the length argument.
We often forget in which category a function falls, here, the “REPLACE” function. Then, we can insert the function as follows:
Choose an empty cell -select the “Insert” tab – click the “Function” option right arrow – click the “All” option right arrow – select the “REPLACE” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Alternatively, we can find the Functions icon to insert the REPLACE in Google Sheets by following the path shown below.
Choose an empty cell – click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
Here, click the “Functions” option – click the “All” option right arrow – select the “REPLACE” function, as shown below.
Download Template
This article must help understand REPLACE in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is REPLACE In Google Sheets. We learn its syntax and how to use it to replace old data with new along with examples. You can learn more from the following articles. –
Leave a Reply