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

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.

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:

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:

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:

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:

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)