How to Find and Replace Multiple Values in Google Sheets

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.

Find and Replace Multiple Values in Google Sheets


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.

Using Find and Replace Tool to Find and Replace Text in Google Sheets

  • 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”.

Using Find and Replace Tool to Find and Replace Multiple Values in Google Sheets

  • 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”.

Using Find and Replace Tool

  • 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”.

Applying SUBSTITUTE Formula to Find and Replace Different Values at a Time in Google Sheets

  • 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.

Applying SUBSTITUTE Formula to Find and Replace various values at a time in google sheets

  • 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”.

Inserting REGEXREPLACE Function to Replace Values in Google Sheets

  • 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.

Inserting REGEXREPLACE Function to Find and Replace Different values in google sheets

  • 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.

Embedding XLOOKUP Function to find and replace multiple values in google sheets

  • In Cell C5, apply the following formula and press Enter
=XLOOKUP(B5,$E$5:$E$7,$F$5:$F$7,B5)

Embedding XLOOKUP Function to Find and Replace Multiple Values in Google Sheets

  • 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.

Embedding XLOOKUP Function to Find and Replace Various Texts in Google Sheets


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.

Select Proper Range in Find and Replace Tool

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”.

Select Proper Range in Find and Replace Tool in Google Sheets

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”.

Unmark Match Entire Cell Contents to Find and Replace Multiple Values in Google Sheets

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”.

Unmark Match Entire Cell Contents to Find and Replace Multiple Values in Google Sheets

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.

Unmark Match Entire Cell Contents To Find and Replace Multiple values in google sheets


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.


Related Article

Adrey

Adrey

Hello! This is Raiyan Zaman Adrey, a fresh graduate in BSc in Civil Engineering from Bangladesh University of Engineering and Technology. From my high school I have been using Excel in which I was always interested and had fun and this led me to do more research on Excel and Google Sheets as both are kinda similar platform. I have always been enthusiastic, self-motivated, reliable and hard working person and for a long time, I am trying to improve myself more and more so that I can face any challenging situation and adapt myself to any environment.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo