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.

How to Find And Replace Multiple Values in Excel: 6 Quick Approaches

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

In Excel, Find and Replace is the most useful tool when you 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.

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

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

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.

You’ll see the following outputs immediately.

Read More: How to Find And Replace Values Using Wildcards 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.

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

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

Read More: How to Find and Replace Using Formula 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.

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

Read More: How to Find and Replace Text Color 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 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.

In the first output Cell C5, the required formula with the REPLACE function will be:

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.

Read More: How to Find and Replace in Excel Column

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.

In the first output Cell C5, the related formula will be:

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.

🔎 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 to 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 simultaneously. If the function cannot find the given values then the old texts will remain the same as before in the New Text column.

So, the required formula with the XLOOKUP function in the first output Cell C5 should be:

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

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, 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:

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.

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.

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.

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

Like in the screenshot below, you’ll see the new and modified texts in Column B under the Text header.

Download Practice Workbook

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

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.

Related Articles

• How to Find and Replace Asterisk (*) Character in Excel
• Find and Replace Tab Character in Excel
• [Fixed!] Excel Find and Replace Not Working
• How to Replace Special Characters in Excel
• How to Substitute Multiple Characters in Excel

<< Go Back to Find and Replace | Learn Excel