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

In Microsoft Excel, it’s a usual scenario to find and replace multiple text or numeric values. You can use the Find and Replace command, apply different functions and formulas, or you can even make user-defined tools to serve the purposes. In this article, you’ll get to know all possible and quick techniques to find and replace multiple values with simple examples and proper explanations.


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.


6 Quick Approaches to Find And Replace Multiple Values in Excel

1. Use Find And Replace Tool for Multiple Values in Excel

In Excel, Find and Replace is the most useful tool when you’ll have to replace a value. We can use it for different purposes. Let’s see how this tool works for various criteria in the following sub-sections.

i. Find And Replace Text Values

In the table below, some texts are lying in Column B. Assuming that we want to replace the value ‘2020’ with ‘2021’ in all texts.

Use Find And Replace Tool for Multiple Values in Excel

📌 Steps:

➤ Press CTRL+H, 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 Replace All button.

Use Find And Replace Tool for Multiple Values in Excel

Like in the picture below, you’ll find all texts with 2021 at the beginning which were 2020 before.

Use Find And Replace Tool for Multiple Values in Excel


ii. Find And Replace with Wild Characters

Now we have different numeric values at the beginning of the following texts. But all of them have a specific format ‘20XX’. What we’ll do here is go for the wildcard search for that numeric format and the last two digits will be replaced with ‘21’. We have to use two Question marks (??) as wildcard characters for the last two digits in the Find and Replace tool.

📌 Steps:

➤ Press CTRL+H again 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


iii. Find And Replace Formulas

In the following table, we have now some sales data for successive 5 days. In Cell C11, the total sales value is present but let’s say we have to find out the average of the sales data there. We don’t have to overwrite the formula there with the AVERAGE function. We’ll use the Find and Replace tool here to replace the formula more easily.

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

➤ Type ‘=AVERAGE’ in the Replace with box.

➤ Press Find Next first and then 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 at once.

Use Find And Replace Tool for Multiple Values in Excel


iv. Find And Replace Cell Formats

We can also change the cell formats with the Find and Replace tool. In the following table, there are some rows with a specific color. We’ll replace the color with another one, let’s say it’s green.

📌 Steps:

➤ Open the Find and Replace dialog box first.

➤ In front of the Find what box, click on the Format option and select the color that has been used in some random cells in the table.

➤ Click on the second Format tab and choose another color you want to replace with the old one.

➤ Press Replace All.

Use Find And Replace Tool for Multiple Values in Excel

In the screenshot below, you’re now seeing the specific rows in the table with the new color.

Use Find And Replace Tool for Multiple Values in Excel


2. Insert REPLACE Function to Find And Replace Multiple Values in Excel

If you don’t want to use the Find and Replace tool then you can go for the REPLACE function. You have to apply this function in a new column or in a range of cells where old text data will be replaced with the new ones. With the process, you can preserve the old text data as well.

In the following picture, two columns are present where 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

After pressing Enter and using Fill Handle to autofill the rest of the cells, you’ll get the new text values right away. Here, we have replaced the value ‘2021’ with ‘2022’ for all texts.

Insert REPLACE Function to Find And Replace Multiple Values in Excel


3. Apply Nested SUBSTITUTE Formula to Find And Replace Multiple Values

The SUBSTITUTE function replaces existing text with a new text in the text string. We can nest the SUBSTITUTE function for multiple values to be replaced.

In the following picture, Column B is lying with 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 related formula will be:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5:B10, E5, F5), E6, F6), E7, F7)

Apply Nested SUBSTITUTE Formula to Find And Replace Multiple Values

Now press Enter and you’ll get an array with the new text values at once. In this formula, we’ve used the SUBSTITUTE function thrice as we had to replace three different values lying under the Find header in Column E.

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

4. Use XLOOKUP Function to Search And Replace Multiple Values in Excel

If you’re an Excel 365 user then you can go for the XLOOKUP function. The XLOOKUP function searches a range or an array for a match and returns the corresponding item the second range or array.

In the following dataset, there are some text values in the Old Text column. The second table on the right represents data that are to be looked for and the ones to be replaced with simultaneously. If the function cannot find the given values then the old texts will remain the same as before in the New Text column.

Use XLOOKUP Function to Search And Replace Multiple Values in Excel

So, the required formula with the XLOOKUP function 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

After pressing Enter and auto-filling the entire column, you’ll be displayed the following outputs right away.

Use XLOOKUP Function to Search And Replace Multiple Values in Excel


5. Combine IFNA And VLOOKUP Functions to Find And Substititue Multiple Values

Now we’ll use an alternative formula to the XLOOKUP function and this formula is available in all Excel versions as well. We have to combine IFNA and VLOOKUP functions here.

The VLOOKUP function looks for a value in the leftmost column of a table and then returns a value in the same row from the specified column. As the VLOOKUP function does not carry any message in case of the lookup value is not found, so it’ll return a #N/A error. To fix that problem, we have to use the IFNA function to customize and define an error message.

So, the required formula incorporating both IFNA and VLOOKUP functions 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


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

In the last section, we’ll apply the VBA codes to formulate a user-defined function. In the following dataset, the text values in Column B will be modified by replacing the numeric values at the beginning. The values that are to be replaced and the new values are lying in the rightward table.

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

Now let’s go through the following procedures to make the user-defined tools and functions:

📌 Step 1:

➤ Right-click your mouse on the Sheet name first.

➤ Select the option ‘View Codes’. A VBA window will appear.

➤ Now 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.

➤ Now select the old texts that you have to modify and press OK.

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

📌 Step 2:

➤ The second dialog box will now open up and you have to select the entire table range (D5:E7) lying on the right in the picture.

➤ Press OK.

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

Like in the screenshot below, 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


Concluding Words

I hope, all of these methods mentioned above will now help you to apply them in your Excel spreadsheets when you have to replace multiple text data effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.


You May Also Like to Explore

Find Matches or Duplicate Values in Excel (8 Ways)

How to Use FIND Function in Excel (7 Suitable Examples)

How to Find Top 5 Values and Names in Excel (8 Useful Ways)

Find Duplicates in Two Columns in Excel (6 Suitable Approaches)

INDEX-MATCH Formula to Find Minimum Value in Excel (4 Suitable Ways)

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo