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.

**ðŸ“Œ**** 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.

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

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

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

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

`=REPLACE(B5,1,4,2021)`

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.

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

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

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

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)`

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, 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)`

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.

**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)**