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.

**Table of Contents** hide

Download Practice Workbook

6 Quick Approaches to Find And Replace Multiple Values in Excel

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

i. Find And Replace Text Values

ii. Find And Replace with Wild Characters

iii. Find And Replace Formulas

iv. Find And Replace Cell Formats

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

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

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

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

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

Concluding Words

**Download Practice Workbook**

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

**Find And Replace Multiple Values.xlsx**

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

**Read More:** How to Replace Text in Excel Formula (7 Easy Ways)

**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 (4 Examples)

**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 Multiple Words at Once in Excel (7 Methods)

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

**Read More:** How to Find and Replace Multiple Words from a List 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.

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

**Read More:** How to Use the Substitute Function in Excel VBA (3 Examples)

**Similar Readings**

- How to Replace Special Characters in Excel (6 Ways)
- Replace Text of a Cell Based on Condition in Excel (5 Easy Methods)
- How to Substitute Multiple Characters in Excel (6 Ways)
- [Fixed!] Excel Find and Replace Not Working (6 Solutions)

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

**Read More:** How to Find and Replace Values in Multiple Excel Files (3 Methods)

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

**Read More:** How to Find and Replace in Excel Column (6 Ways)

**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 ExplicitSub FindnReplaceMultipleValues()Dim Rng As RangeDim OldText As RangeDim ReplaceData As RangeOn Error Resume NextSet OldText = Application.InputBox("Select Old Text Range:", "Find And Replace Multiple Values", Application.Selection.Address, Type:=8)Err.ClearIf Not OldText Is Nothing ThenSet ReplaceData = Application.InputBox("Replace What And With:", "Find And Replace Multiple Values", Type:=8)Err.ClearIf Not ReplaceData Is Nothing ThenApplication.ScreenUpdating = FalseFor Each Rng In ReplaceData.Columns(1).CellsOldText.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).ValueNextApplication.ScreenUpdating = TrueEnd IfEnd IfEnd 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.

**Read More:** How to Find and Replace Multiple Values in Excel with VBA (3 Examples)

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

- How to Find and Replace within Selection in Excel (7 Methods)
- Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)
- Excel VBA: How to Find and Replace Text in Word Document
- How to Find and Replace from List with Macro in Excel (5 Examples)
- Excel VBA to Find and Replace Text in a Column (2 Examples)