Find And Replace Multiple Values in Excel (6 Quick Methods)

Method 1 – Use the Find and Replace Tool for Multiple Values in Excel

Case 1 – Find and Replace Text Values

In the table below, we want to replace the value ‘2020’ with ‘2021’ in all cells.

Use Find And Replace Tool for Multiple Values in Excel

Steps:

  • Press Ctrl + H, and the Find and Replace dialog box will open up.
  • Type ‘2020’ in the Find what box.
  • In the Replace with box, type ‘2021’.
  • Click on the Replace All button.

Use Find And Replace Tool for Multiple Values in Excel

  • The function changes all the instances of 2020 with 2021 throughout the sheet.

Use Find And Replace Tool for Multiple Values in Excel


Case 2 – Find and Replace with “?” for Wild Characters

We have different numeric values at the beginning of the following texts, but all of them have a specific format ‘20XX’. The last two digits will be replaced with ‘21’.

Steps:

  • Press Ctrl + H to open the Find and Replace dialogue box.
  • In the Find what box, type ‘20??’.
  • Input the value ‘2021’ in the Replace with box.
  • Press Replace All and you’re done.

Use Find And Replace Tool for Multiple Values in Excel

  • You’ll see the following outputs immediately.

Use Find And Replace Tool for Multiple Values in Excel


Case 3 – Find and Replace Formulas

We have some sales data for 5 days. Cell C11 contains the total value, but we want to find out the average of the sales data there.

Use Find And Replace Tool for Multiple Values in Excel

Steps:

  • Open the Find and Replace dialog box.
  • In the Find what box, type ‘=SUM’.
  • Copy ‘=AVERAGE’ in the Replace with box.
  • Press Find Next and click on the Replace button.

Use Find And Replace Tool for Multiple Values in Excel

  • In the output Cell C11, you’ll get the new calculated result.

Use Find And Replace Tool for Multiple Values in Excel

Read More: How to Find and Replace Using Formula in Excel


Case 4 – Find And Replace Cell Formats

In the following table, there are some rows with a specific color. We’ll replace the color with another one, let’s say green.

Steps:

  • Open the Find and Replace dialog box.
  • In front of the Find what box, click on the Format option and select the color that has been used in the cells in the table.
  • Click on the second Format tab and choose the new color.
  • Press Replace All.

Use Find And Replace Tool for Multiple Values in Excel

  • All the applicable cells will be reformatted.

Use Find And Replace Tool for Multiple Values in Excel

Read More: How to Find and Replace Text Color in Excel


Method 2 – Insert the REPLACE Function to Find and Replace Multiple Values in Excel

In the following picture, the column with the New Text header will display the modified texts.

Insert REPLACE Function to Find And Replace Multiple Values in Excel

  • In the first output Cell C5, the required formula with the REPLACE function will be:
=REPLACE(B5,1,4,2021)

Insert REPLACE Function to Find And Replace Multiple Values in Excel

  • Press Enter and use Fill Handle to autofill the rest of the cells, and you’ll get the new text values. We have replaced the values ‘2021’ with ‘2022’ for all texts.

Insert REPLACE Function to Find And Replace Multiple Values in Excel

Read More: How to Find and Replace in Excel Column


Method 3 – Apply a Nested SUBSTITUTE Formula to Find and Replace Multiple Values

In the following picture, Column B has some random text data. The table on the right represents the values that have to be replaced with the new ones.

Apply Nested SUBSTITUTE Formula to Find And Replace Multiple Values

  • In the first output Cell C5, the formula will be:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5:B10, E5, F5), E6, F6), E7, F7)

Apply Nested SUBSTITUTE Formula to Find And Replace Multiple Values

  • Press Enter and you’ll get an array with the new text values.

Apply Nested SUBSTITUTE Formula to Find And Replace Multiple Values

How Does the Formula Work?

  • The innermost SUBSTITUTE function replaces the value ‘2018’ with ‘2019’.
  • The second SUBSTITUTE function looks for ‘2020’ and replaces it with ‘2021’.
  • The outer SUBSTITUTE function searches ‘2022’ and substitutes it with ‘2023’.

Method 4 – Use the XLOOKUP Function to Search and Replace Multiple Values in Excel

If you’re an Excel 365 user, you can also use the XLOOKUP function.

In the following dataset, there are some text values in the Old Text column. The second table on the right represents data that needs to be searched for and replaced. If the function can’t find the given values, then the old content will remain.

Use XLOOKUP Function to Search And Replace Multiple Values in Excel

  • The required formula in the first output Cell C5 should be:
=XLOOKUP($B5,$E$5:$E$10,$F$5:$F$10,$B5)

Use XLOOKUP Function to Search And Replace Multiple Values in Excel

  • Press Enter and auto-fill the entire column.

Use XLOOKUP Function to Search And Replace Multiple Values in Excel


Method 5 – Combine IFNA and VLOOKUP Functions to Find and Substitute Multiple Values

  • The required formula in the output Cell C5 will be:
=IFNA(VLOOKUP($B5,$E$5:$F$10,2,FALSE),B5)

Combine IFNA And VLOOKUP Functions to Find And Substititue Multiple Values

  • After pressing Enter and filling down the rest of the cells in Column C, we’ll get all the new text data as shown in the picture below.

Combine IFNA And VLOOKUP Functions to Find And Substitute Multiple Values


Method 6 – Embed VBA Code to Make a UDF to Find and Replace Multiple Values

In the following dataset, the text values in Column B will be modified by replacing the numeric values at the beginning. These values are in the table on the right.

Embed VBA Codes to Make a UDF to Find And Replace Multiple Values

Steps:

  • Right-click on the Sheet name.
  • Select the option ‘View Code’. A VBA window will appear.
  • Paste the following codes there:
Option Explicit
Sub FindnReplaceMultipleValues()
  Dim Rng As Range
  Dim OldText As Range
  Dim ReplaceData As Range
  On Error Resume Next
  Set OldText = Application.InputBox("Select Old Text Range:", "Find And Replace Multiple Values", Application.Selection.Address, Type:=8)
  Err.Clear
  If Not OldText Is Nothing Then
    Set ReplaceData = Application.InputBox("Replace What And With:", "Find And Replace Multiple Values", Type:=8)
    Err.Clear
    If Not ReplaceData Is Nothing Then
      Application.ScreenUpdating = False
        For Each Rng In ReplaceData.Columns(1).Cells
          OldText.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value
        Next
      Application.ScreenUpdating = True
    End If
  End If
End Sub
  • Press F5 and a dialogue box as shown in the screenshot below will appear.
  • Select the old cells that you have to modify and press OK.

Embed VBA Codes to Make a UDF to Find And Replace Multiple Values

  • A second dialog box will open. Select the entire table range (D5:E7) on the right.
  • Press OK.

Embed VBA Codes to Make a UDF to Find And Replace Multiple Values

  • You’ll see the new and modified texts in Column B under the Text header.

Embed VBA Codes to Make a UDF to Find And Replace Multiple Values


Download the Practice Workbook


Related Articles


<< Go Back to Find and Replace | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo