Jawadul Islam Chowdhury
We usually use the Find and replace feature in Google Sheets to search through cell contents and results of formulas and replace them with desired values or data. But sometimes we need to search within the formula to replace inputs. There is an option for search and replace within the formulas. This article will teach you how to find and replace within the Google Sheets formula.
Step-by-Step Procedure to Find and Replace Within Formula in Google Sheets
We have a dataset for stationery items like books, calculators, and notebooks bought from a store along with their quantity, unit price, and total price. We will find the “ only” string within the formula and replace it with an empty string “ ”.
Follow the steps below to do that by yourself.
📌 Step 1: Open Find and Replace Window
- First, go to Edit >> Find and replace from the ribbon. You can also use the CTRL + H shortcut to do that.
- Then the Find and replace dialog box will appear.
Read More: How to Find and Replace Blank Cells in Google Sheets
📌 Step 2: Input Text to Find and Replace
- Next, enter “ only” in the Find box.
- After that, remove everything from the Replace with.
Read More: How to Find and Replace Multiple Values in Google Sheets
📌 Step 3: Choose Search Options
- Then, select “This sheet” to Search within the current sheet only.
- Next, check the Also search within formulae.
📌 Step 4: Find and Replace Within Formula
- Now you can find and replace the “ only” string within formulas one by one by clicking on the Find and Replace.
- Find and replace operations will be done one by one following this technique.
- Or you can replace all the results using the option Replace all. A notification will appear that will tell you how many instances are founded and replaced. Press Done to close the dialogue box.
- Then the formulas will change as follows.
- The final output will look like this.
How to Find and Replace in Google Sheets Using Formula
You can use formulas to find and replace values and texts in Google Sheets. In this section, you will learn how to find and replace using formulas.
1. Using SUBSTITUTE Function
The SUBSTITUTE function can replace the texts in the cells with other strings in Google Spreadsheets. There are three parameters for this function: text to search, which is ranges or cells in which we will execute the formula, search for, and replace with.
📌 Steps:
- First, select the cell (F5) in which you want to input the function.
- Then, type the formula, which is =SUBSTITUTE(.
- Now, for 1st parameter, you have to define the ranges of cells to look in. So select cell E5 and put a comma after that.
- After that, for 2nd parameter, you have to input the word you are searching to replace. So enter “ only” and put a comma after that.
- Finally, for 3rd parameter, type the word you want to replace with. So enter “ ” and put an open bracket.
=SUBSTITUTE(E5," only","")
- Now press enter and then drag the Fill Handle tool to copy the formula to the rest of the cells.
- The final output will look like the following screenshot.
2. Applying REGEXREPLACE Function
The REGEXREPLACE function is one of the regex functions. Using this function you can replace a string with a different text string. There are three parameters to include while using this function: text, expression, and replacement. Follow the steps below to replace strings within formula outputs.
📌 Steps:
- First, select the cell in which you want to input the function.
- After that, type =REGEXREPLACE( to insert the function.
- Now for 1st parameter, you have to define the ranges of cells to look in. For our case, select E5 and put a comma after that.
- Similarly, for 2nd parameter, input the word you are searching to replace. For our case, enter “ only” and add a comma after that.
- Then, for 3rd parameter, type the word you want to replace with. For the case, you have to enter “ ” and put an open bracket after that.
=REGEXREPLACE(E5," only"," ")
- Now press enter to replace the string. Then, drag the Fill Handle tool to use the formula in the rest of the cells.
- Finally, the output will look like the following screenshot.
Things to Remember
- When using the Replace all option, be cautious because similar strings from other cells in the sheet that are not formulas will also be changed.
- To open the Find and replace box directly you can use the shortcut CTRL + H.
Conclusion
This article covers all the steps to follow while performing the find and replace within the Google Sheets formula. Hopefully, following the article, you will learn how to use the option. Please comment below for further queries or suggestions. You can find more Google Spreadsheet-related blogs on our OfficeWheel blogs.
