7 Ways to Find and Replace Multiple Values in Excel

In this lesson, I’ll demonstrate 7 effective approaches for finding and replacing multiple values in Excel. These techniques will enable you to easily find and replace multiple values over huge datasets in Excel. You will master several crucial Excel features and functions during this article, which will come in extremely handy for any assignment using Excel.


📁 Download Excel File

The sample worksheet that was used during the discussion is available to download for free right here.


Learn to Find and Replace Multiple Values in Excel Using These 7 Methods

I have demonstrated 7 approaches to find and replace multiple values in Excel in this Article. Every Approach is detailed with accurate step-by-step pictures. Therefore Going through all the methods you will better understand which one to use in a specific situation for efficiency. The Excel file used for all the solutions down below is also attached to this article it will help you to understand better.


Method 1

1. Using Find and Replace Tool

This is one of the easiest approaches to find and replace a certain value in Excel. We will be using Find and Select Tool and complete our desired task of replacing data.

⬇️⬇️ STEPS ⬇️⬇️

  • First, we will select the Find and Select Tool from the ribbon, it’s found in the Home option and located at the rightmost side of the ribbon. We will select the Replace option from the Find and Select Tool like the picture.

Find and replace using Find and replace tool in Excel

  • Now a new window will appear, type in what you want to find from the worksheet and insert the replacing value. Then simply click on Replace All.  The Find All button will show you details of every cell that has the value which you are searching for.

 excel find and replace multiple values using find and replace tool

  • For instance, you’ll see the following Scenario.

 excel find and replace multiple values results

  • We have successfully replaced multiple values in an Excel datasheet.

📕 Read More: 3 Quick Ways to Replace Text After Specific Character


Method 2

2. Using SUBSTITUTE Function

The Substitute Function can be handy in solving this find and replacing multiple values Excel problem. We will be using a formula using multiple Substitute Functions. The process is given below with pictures.

⬇️⬇️ STEPS ⬇️⬇️

  • First, you need to make a list of the values you are willing to replace as well as the values you are going to replace with. Then you need to select a cell where you want your answers to store. Then simply copy the following formula.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B$6:$B$12, $D$6, $E$6),$D$7, $E$7), $D$8, $E$8), $D$9, $E$9)

excel find and replace multiple values Using Substitute function

  • Now after that simply tap Enter.

 excel find and replace multiple values using substitute result

 excel find and replace multiple values Results Substitute function

Here we replaced 4 values which are short-term of the name of some states to their actual form.

🔨 Formula Breakdown

👉  SUBSTITUTE($B$6:$B$12, $D$6, $E$6)

The First part of the command $B$6:$B$12 will result in selecting the text or reference to a cell that contains text for which we want to substitute characters and it’s column B, then the next term $D$6 the old text, and finally $E$6 is the text we want to replace the old text with if it’s found on the reference cell of the row.

👉  SUBSTITUTE(SUBSTITUTE($B$6:$B$12, $D$6, $E$6),$D$7, $E$7)

This will search for other options to match. Here we don’t get a match as $D$7 doesn’t have any data that is in the reference cell $B$6  for row 6.

👉  SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B$6:$B$12, $D$6, $E$6),$D$7, $E$7), $D$8, $E$8)

This command will also search for other options to match. Here we don’t get a match as $D$8 doesn’t have any data that is in the reference cell $B$6 for row 6.

👉  SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B$6:$B$12, $D$6, $E$6),$D$7, $E$7), $D$8, $E$8), $D$9, $E$9)

Lastly, this command will again search for other options to match. Here we don’t get a match as $D$9 has no information that corresponds to the reference cell $B$6 for row 6.

📕 Read More: 13 Ways to Search for Text in Range in Excel


Method 3

3. Applying XLOOKUP Function

The XLOOKUP Function is a good option if you use Excel 365. The XLOOKUP Function looks for matches between two ranges or arrays and returns the matching item from the first range or array. We will be using the previous dataset in this approach also.

⬇️⬇️ STEPS ⬇️⬇️

  • Start with selecting the cell you want the result from.
  • Now carefully type or copy the following formula.
=XLOOKUP($D6,$B$6:$B$10,$C$6:$C$10,$D6)

 excel find and replace multiple values using Xlookup function

  • Hit Enter for the result.

 excel find and replace multiple values Xlookup results

 excel find and replace multiple values Xlookup function all results

📕 Read More: How to Find and Replace Using Wildcards in Excel


Method 4

4. Utilizing LAMBDA Function

Excel offers a specific function that enables building customized functions using a conventional formula syntax For Microsoft 365 subscribers. This function named LAMBDA being a special feature doesn’t work on every version of Excel. So use it if you have Microsoft 365 Subscription. In other words, a very handy feature for excel power users. We are going to demonstrate the function below. We are using this function here to find and replace multiple values.

⬇️⬇️ STEPS ⬇️⬇️

  • In the beginning, use the ribbon to go to the Name Manager from the Formulas.

 excel find and replace multiple values How to use Lambda function Name manager Formula tab

  • After clicking on Name Manager, a new window will appear Select New for defining a new function.

Name manager for Lambda Function

  • Now another window will pop up. Give the custom function a Name. I am using MultiReplace for instance. Next copy down the following formula and paste in the Refers to: option and press OK.

=LAMBDA(text_value, old_value, new_value, IF(old_value<>””, MultiReplace(SUBSTITUTE(text_value, old_value, new_value), OFFSET(old_value, 1, 0), OFFSET(new_value, 1, 0)), text_value))

excel find and replace multiple values Lambda Function creating custom function

 

  • Now select the cell you want to store the replaced data and copy down the following formula. Here we are using our freshly baked function.
=MultiReplace(B6:B12,$D$6,$E$6)

 excel find and replace multiple values Using Custom Function Lambda

  • Press Enter for the result.

Replacing data with custom function results

all results for custom function

🔨 Formula Breakdown

👉  LAMBDA(text_value, old_value, new_value, IF(old_value<>””, MultiReplace(SUBSTITUTE(text_value, old_value, new_value), OFFSET(old_value, 1, 0), OFFSET(new_value, 1, 0)), text_value))

➡️  This command will define a new function named MultiReplace as well as variables with text_value, old_value and new_value.

👉  OFFSET(old_value, 1, 0)

➡️  That will give you data which you want to replace.

👉  OFFSET(new_value, 1, 0)

➡️  This part of the code is for the data which are gonna replace other data.

👉  IF(old_value<>””, MultiReplace(SUBSTITUTE(text_value, old_value, new_value), OFFSET(old_value, 1, 0), OFFSET(new_value, 1, 0)), text_value))

➡️  Here we are telling what to do if our selected text_value matches with our old_value data which is replacing with the new_value. Also what to do if nothing matches which is keeping the text_value intake.

👉  MultiReplace(B6:B12,$D$6,$E$6)

➡️  It’s the newly defined function here we are dealing with. The first part B6:B12 is our range dataset. $D$6 refers to old_value and $E$6 refers to new_value.

Out-turn: NY, New Jersey

📕 Read More: 3 Easy Ways to Find and Replace in Multiple Excel Files


Method 5

5. With Replace Function

The Replace Function is an easy approach for replacing specific values in cells. By this function, we can replace by denoting specifically what to replace by its position in the data and also what is the total number of that character which will be replaced later, in a dataset.

We have here a dataset that we are gonna make smaller by replacing 2010 to 10.

⬇️⬇️ STEPS ⬇️⬇️

  • Select the cell in which we are gonna get the result.
  • After that type in the following formula.
=REPLACE(B6,1,4,10)

 excel find and replace multiple values using replace Function

  • Here we can see that 2010 is replaced with 10.

Replace function results

Replace function results


Method 6

6. Combination of VLOOKUP And IFNA Functions

VLOOKUP Function is handy for finding certain values in a worksheet. You can use this function by following the format down below

=VLOOKUP(What to look up, where to look for it ( The Range ), which column in the range has the value which you want to show, and whether to return an approximate or exact match, denoted as 1/TRUE or 0/FALSE)

Here in this example we have 7 short names for States. We want to abbreviate only 4 of them by replacing values. So we will be using a combination of IFNA and VLOOKUP Functions to get our result

⬇️⬇️ STEPS ⬇️⬇️

  • We are choosing the cell in which we want the answer.

Selecting cell for excel find and replace multiple values

  • After that Copy the formula down below which is the combined formula.
=IFNA(VLOOKUP($B6,D6:E9, 2, FALSE), B6)

excel find and replace multiple values Combination of IFNA And VLOOKUP Functions

  • You will have the following result by pressing Enter.

IFNA VLOOK function results

Combination of IFNA And VLOOKUP Functions All results find and replace multiple values

🔨 Formula Breakdown

👉  VLOOKUP($B6,D6:E9, 2, FALSE)

➡️  The first part of the VLOOKUP function $B6 says what we want look for here, next section defines where to look for it which is D6:E9. Then comes which column in the range has the value which you want to show which is for us, lastly FALSE for EXACT MATCH.

👉  IFNA(VLOOKUP($B6,D6:E9, 2, FALSE), B6)

➡️  The IFNA decides what to show if no value is matched which is B6.

Out-turn: New Jersey.


Method 7

7. Using VBA Code

We are going to use a VBA code to solve our problem of changing multiple values by replacing them with a specific value. The text contents within Column B of the underlying dataset will now be changed by swapping out the initial numerical values. The table on the right side contains both the old values that you will replace and also, the new values.

⬇️⬇️ STEPS ⬇️⬇️

  • First, go to the bottom left side and then right-click on the top of the sheet name you are working on and click on the View Code.

How to add VBA code from sheets option

  • Now a new window will open up. Copy the following VBA code there.
Option Explicit
Sub FindnReplaceMultipleValues()
  Dim RngVal As Range
  Dim InputText As Range
  Dim OutputText As Range
  On Error Resume Next
  Set InputText = Application.InputBox("Range of Replacable Data:", "Finding And Replacing Multiple Values", Application.Selection.Address, Type:=8)
  Err.Clear
  If Not InputText Is Nothing Then
    Set OutputText = Application.InputBox("Replace Data:", "Finding And Replacing Multiple Values", Type:=8)
    Err.Clear
    If Not OutputText Is Nothing Then
      Application.ScreenUpdating = False
        For Each RngVal In OutputText.Columns(1).Cells
          InputText.Replace what:=RngVal.Value, replacement:=RngVal.Offset(0, 1).Value
        Next
      Application.ScreenUpdating = True
    End If
  End If
End Sub
Find and replace VbA code
  • Now hit A new window that will be created like the following picture which wants you to state cells or ranges which you want to change or replace. We are selecting $B$6:$B$12 for column B. Select OK for confirming.

Use of VBA code for excel find and replace multiple values

  • Now we have a new window that says Find And Replace Multiple Values so we have to let the code know what to find and what to replace. So we are selecting the table by which we want the change. So we are selecting $D$6:$E$9. Then OK for confirmation.

VBA code process for excel find and replace multiple values

  • You are done and then you may see the following results if you have done everything properly.

excel find and replace multiple values Results for VBA


📄 Important Notes

🖊️  Be careful while using the code as there are a lot of terms and if you lose concentration the code may be incorrect.

🖊️  You can open the VBA window in a lot of different ways and look at what’s below for instance.

🖊️  While writing formulas carefully see the suggestions and don’t forget the commas and parentheses.

🖊️  Use ”$” for absolute value.


📝 Takeaways from This Article

📌  Combining XLOOKUP, VLOOKUP, REPLACE, IFNA, LAMBDA, SUBSTITUTE functions and then how to write formulas.

📌  How to write a VBA code.

📌  The Find and Select Tool and consequently it’s usage.

📌  Finding and Replacing multiple values in Excel.

📌  Different ways of selecting the Macros option for VBA.


Conclusion

I hope you were able to use the techniques I demonstrated in this Excel lesson to find and replace multiple values. Therefore, you can see, there are a lot of options on how to do this. Decide deliberately on the approach that best addresses your circumstance. In addition I advise repeating the steps if you become confused in any of the steps if you get stuck. Practice on your own after taking a look at find and replace multiple values Excel file in the practice workbook as I’ve provided above because practice makes a man perfect. Therefore, I earnestly hope you can use it. Please share your experience with us in comments about how your experience was throughout the article and what we need to improve or add to make things better for you folks. However, if you have any problem regarding Excel spit it in comment box. The Excelden crew is always available to answer your questions. In conclusion, keep educating yourself by reading. For more Excel-related articles please visit our website Excelden.com.


Related Article

(Visited 102 times, 1 visits today)
Nashid

Nashid

Hello there. I am Nashid, an Engineer. I Love music, traveling, sports and gaming. I have recently grown interest in DATA SCIENCE and wish to build a career over it. Excel is one of the greatest modern digital creation of human. Life simply gets easier. My Articles are meant to lead you to that easier life with efficiency. If you need any kind of help regarding my articles even anything else try contacting me on social platforms of which I've provided. Keep learning. Wish me luck and Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo