Adrey
Sometimes we input wrong or improper data subconsciously, knowingly or by any kind, and eventually it comes up to correct them all. Obviously, we have no time for correcting them one by one. This article is to illustrate 4 quick methods to Find and Replace Multiple Values in Google Sheets to you.
A Sample of Practice Spreadsheet
4 Quick Methods to Find and Replace Multiple Values in Google Sheets
We will use the following sample dataset to demonstrate these methods accurately. The dataset represents some fruits’ names.
1. Using Find and Replace Tool
The Find and Rheplace feature in Google Sheets enables us to substitute a specific value with another text. This tool lets us replace every occurrence of a specific search value at once or one at a time.
Steps:
- Suppose, we want to replace the word “Apple” with “Honeycrisp Apple” from column B.
- At first from the toolbar, select Edit and then select Find and Replace.
- Or we can use Keyboard Shortcuts Ctrl+H for this function too.
- The following window will pop up on your screen-
- Here, at the “Find” box type “Apple”, at the “Replace with” box type “Honeycrisp Apple”, select “This Sheet” from the Search menu, and select “Match case”.
- Now, if you press “Find”, this will show you all the cells with the word “Apple” one by one.
- And then, press “Replace all” and all the words “Apple” will be replaced with “Honeycrisp Apple”.
- Finally, press “Done” and the pop-up window of the “Find and Replace” tool will be gone.
Read More: How to Find and Replace Blank Cells in Google Sheets
2. Applying SUBSTITUTE Formula
To identify and replace values one at a time, this formula combines several SUBSTITUTE functions together. The outcome of the first substitution is sent into the second SUBSTITUTE function, and so on. Here we will substitute 3 values. SUBSTITUTE functions can be combined in the formula to create extra substations to Find and Replace multiple values in Google Sheets.
Steps:
- Assume that we want to replace “Banana” with “Cavendish Banana”, “Apple” with “Honeycrisp Apple” and “Mango” with “Alphonso Apple”.
- Here we selected Cell C5. In Cell C5 apply the following formula and press Enter–
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,"Banana","Cavendish Banana"),"Apple","Honeycrisp Apple"),"Mango","Alphonso Mango")
Formula Breakdown
- SUBSTITUTE(B5,”Banana”,”Cavendish Banana”)
This function will find the word “Banana” in column B from Cell B5 and will replace all words “Banana” with “Cavendish Banana” and will show results in column C.
- (SUBSTITUTE(SUBSTITUTE(B5,”Banana”,”Cavendish Banana”),”Apple”,”Honeycrisp Apple”)
This will do exactly the same but this time it will replace the both words “Banana” and “Apple” with “Cavendish Banana” and “Honeycrisp Apple” respectively.
- SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,”Banana”,”Cavendish Banana”),”Apple”,”Honeycrisp Apple”),”Mango”,”Alphonso Mango”)
This will replace all the 3 words “Banana”,”Apple” and “Mango” with “Cavendish Banana”,”Honeycrisp Apple” and “Alphonso Mango” respectively.
- Select Cell E5 again, simply drag down selecting the Fill Handle icon as shown in the circled portion.
- And those will be replaced and will show the result as follows in Cell C5:C15.
Read More: How to Find and Replace Within Formula in Google Sheets
3. Inserting REGEXREPLACE Function
The REGEXREPLACE function is similar to the SUBSTITUTION method. Allows us to replace multiple values in Google Sheets.
Steps:
- Presume, we want to replace “Banana” with “Cavendish Banana”, “Apple” with “Honeycrisp Apple” and “Mango” with “Alphonso Apple”.
- Here we selected Cell C5. In Cell C5 apply the following formula and press Enter–
=REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(B5,"Banana","Cavendish Banana"),"Apple","Honeycrisp Apple"),"Mango","Alphonso Mango")
Formula Breakdown
- REGEXREPLACE(B5,”Banana”,”Cavendish Banana”)
This function will find word “Banana” in column B from Cell B5 and will replace all words “Banana” with “Cavendish Banana” and will show results in column C.
- REGEXREPLACE(REGEXREPLACE(B5,”Banana”,”Cavendish Banana”),”Apple”,”Honeycrisp Apple”)
This will do exactly the same but this time it will replace the both words “Banana” and “Apple” with “Cavendish Banana” and “Honeycrisp Apple” respectively.
- REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(B5,”Banana”,”Cavendish Banana”),”Apple”,”Honeycrisp Apple”),”Mango”,”Alphonso Mango”)
This will replace all the 3 words “Banana”,”Apple” and “Mango” with “Cavendish Banana”,”Honeycrisp Apple” and “Alphonso Mango” respectively.
- Select Cell E5 again, simply drag down selecting the Fill Handle icon as shown in the circled portion.
- And those will be replaced and will show the result as follows in Cell C5:C15.
4. Embedding XLOOKUP Function
One of the most efficient functions to Find and Replace multiple values in Google Sheets is XLOOUP function. The XLOOUP function in Google Sheets looks for values within a range and returns corresponding values. Like in the following example, XLOOKUP will look for values that are in Cell E5:E7 and will return their corresponding values that are in Cell F5:F7 in Column B starting from Cell B5.
Steps:
- Let, we want to replace “Banana” with “Cavendish Banana”, “Apple” with “Honeycrisp Apple” and “Mango” with “Alphonso Mango.
- In Cell C5, apply the following formula and press Enter–
=XLOOKUP(B5,$E$5:$E$7,$F$5:$F$7,B5)
- Select Cell E5 again, simply drag down selecting the Fill Handle icon as shown in the circled portion.
- And those will be replaced and will show the result as follows in Cell C5:C15.
What to Do When Find and Replace Operation Is Not Working in Google Sheets?
There are several reasons for the Find and Replace operation not working in Google Sheets. Here I have tried to demonstrate some of the reasons.
Reason 1. Unnecessary Spaces
In the following dataset above, I have tried to replace the word “Apple” with “Honeycrisp Apple”. But there is something showing like “There are no entries matching Apple” below.
It’s because there is extra space after the word “Apple” in the Find box.
Solution:
So, what to do! Simply remove the extra space and it will surely work then.
Reason 2. Wrong Range Selection
Suppose, in the following dataset, I have only selected the last 5 cells.
Then I opened the Find and Replace tool, and wanted to replace the word “Apple” with “Honeycrisp Apple” but again it turned out to be an error and says “No matches found”.
It’s because the word “Apple” doesn’t even exist in the selected cell range. If you select multiple cells manually, the Find and Replace feature will find that word only on those selected cells.
Solution:
Just simply select one cell or select the range properly or don’t select any cell at all. Apply the procedure again and it will surely work now. And that’s it!
Reason 3. Match Entire Cell Contents Is Marked
Let’s assume that in the following dataset we want to replace the word “Kingdom” with “States”.
Now, if you want to find and replace that word marking with Match Entire Cell Contents then it will return as “There are no entries matching Kingdom”.
Such a result will appear because if you mark that option then Find and Replace function will only search for the whole content of a cell, partial search will not work.
Solution:
Simply Unmark that option.
And now the result will be as you wanted.
Conclusion
In this world, all look for competence among themselves. So, we have to be more efficient and quicker. In this article, I have demonstrated 4 easy methods to Find and Replace multiple values in Google Sheets. Hope this gonna help you with your workings. Our site officewheel.com surely will help you to become more efficient and expert in using Google Sheets.
