How to Find and Replace Multiple Words at Once in Excel (7 Methods)

Often we need to replace values. In this article, we’ve shown you 7 methods to find and replace multiple words at once in Excel. A company previously noted its office locations in short forms, now the company wants to note those in full forms.
We’ll solve this problem using a sample dataset and it has three columns: Office#, Office Name, and Office Name Full.

Download Practice Workbook

7 Ways to Find and Replace Multiple Words at Once in Excel

1. Using Find & Replace Command to Replace Multiple Words in Excel

For the first method, we’ll use the Replace command of Excel to replace all the “AL” with “Alabama”.

Steps:

• Firstly, select cell range C5:C10.
• Secondly, from Find & Select >>> select Replace…

Find and Replace dialog box will appear.

• Thirdly, type the following things:
  • Find what: AL.
  • Replace with: Alabama.
• Then, put a tick mark on:
  • Match case.
  • Match entire cell contents.
• After that, select Replace All.

A dialog box will appear.

• Click OK.
• Finally, press Close.

We’ve found and replaced two “AL” into “Alabama”.

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

2. Using Nested SUBSTITUTE Function to Find and Replace Multiple Words at Once in Excel

We’re going to use the SUBSTITUTE function to replace multiple words at once.

Steps:

• Firstly, select cell range D5:D10.
• Secondly, type the following formula.

• Thirdly, press CTRL + ENTER.

Thus, we’ve shown the second method to find and replace multiple words at once.

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

3. Applying the XLOOKUP Function to Find and Replace Multiple Words at Once in Excel

In this method, we’ll use the XLOOKUP function to replace multiple words. Remember, this function is only available in Excel 365 and Office 2021 versions.

Steps:

• Firstly, type the following formula in cell D5.

The XLOOKUP function returns values from a matched array.

Here, $F$5:$F$9 is our lookup_array, and $G$5:$G$9 is our return_array. If the value from the C5 cell is found on the lookup_array, then a value will be returned from the return_array. If we can’t find the value, then the cell value will be returned. Value from cell C5 “AL” is found on the lookup array, therefore, we’ll get “Alabama”.

• Secondly, press ENTER.

Here, the word “AL” is matched with the lookup_array, after that, “Alabama” is obtained from the return_array. Our formula needs another step. We need to put this formula to the other cells. We’re replacing the words in mainly 2 steps.

• Finally, we can use the Fill Handle to AutoFill formula.

In conclusion, the third method works flawlessly to replace multiple words at once.

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

Similar Readings

• How to Find and Replace Asterisk (*) Character in Excel
• Replace Special Characters in Excel (6 Ways)
• How to Find And Replace Values Using Wildcards in Excel
• Data clean-up techniques in Excel: Adding text to cells
• Find And Replace Multiple Values in Excel (6 Quick Methods)

4. Utilizing the VLOOKUP Function to Find and Replace Multiple Words at Once in Excel

In this method, we’re going to use the VLOOKUP function and the IFERROR function to replace multiple words.

Steps:

• Firstly, type the formula from below in cell D5.

We’re using the VLOOKUP function to replace words. If any value is not found, then an error will be shown. Therefore, with the help of the IFERROR function, we’re replacing any error with the corresponding cell value.

Moreover, our lookup array has two columns, we want to return from the second column. Therefore, we used 2 in the formula. We want an exact match, hence we choose FALSE in the formula.

Note: Remember to use an absolute cell reference. Here, we need one extra step to apply the formula to the other cells.

• Secondly, press ENTER.

We’ve changed “AL” to “Alabama”.

• Finally, use the Fill Handle to AutoFill the formula.

We’ve found and replaced the words in Excel.

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

5. Applying the REPLACE Function to Find and Replace Multiple Words at Once

Here, we’ll use the REPLACE function to find and replace multiple words.

Steps:

• Firstly, select cell range D5:D8.
• Secondly, type the following formula.

We’ve selected our cell range C5:C8 as our old_text. The starting location to search in our string is set to 1. We want to replace 2 characters, hence, we’ve picked 2. Finally, we’ve selected F5:F8 as our new_text.

• Thirdly, press CTRL + ENTER.

Note: It is optional to use an absolute cell reference in this case. Our old_text and new_text need to be of the same length, else we’ll get errors.

We’ve replaced multiple words at once in yet another way.

Related Content: How to Find and Replace from List with Macro in Excel (5 Examples)

6. Combining the IF and REPLACE Functions to Find and Replace Multiple Words at Once in Excel 

We can use the IF function, the LEN function, and the REPLACE function to replace multiple words.

Steps:

• Firstly, type the formula from below to cell D5.

• Secondly, press ENTER.

We can see the value is changed in cell D5.

• Finally, AutoFill the formula.

This method is similar to method 5. However, we don’t need another helper table to do our operation. Moreover, this method requires one extra step to replace the values.

Read More: Replace Text of a Cell Based on Condition in Excel (5 Easy Methods)

7. Creating a Custom Function Using VBA to Find and Replace Multiple Words at Once in Excel 

For the last method, we’re going to create a user-defined function using VBA to replace multiple words.

Steps:

• Firstly, press ALT + F11 to bring up the Visual Basic window. Alternatively, you can do it from the Developer tab >>> Visual Basic.
• Secondly, from Insert >>> select Module.

• Thirdly, copy and paste the following code.

Function ReplaceWords(sInput As String, rFind As Range, rReplace As Range) As String

Dim sTemp As String
Dim sFind As String
Dim sReplace As String
Dim cFind As Range
Dim lcFind As Long
Dim lrFind As Long
Dim lrReplace As Long
Dim lcReplace As Long
lcFind = rFind.Columns.Count
lrFind = rFind.Rows.Count
lcReplace = rFind.Columns.Count
lrReplace = rFind.Rows.Count

sTemp = sInput

If Not ((lcFind = lcReplace) And (lrFind = lrReplace)) Then
    ReplaceWords = CVErr(xlErrNA)
    Exit Function
End If

For Each cFind In rFind
    sFind = cFind.Value
    sReplace = rReplace(cFind.Row - rFind.Row + 1, cFind.Column - rFind.Column + 1).Value
    sTemp = Replace(sTemp, sFind, sReplace)

Next cFind
ReplaceWords = sTemp
End Function

We’re creating a custom function named “ReplaceWords”. This function has three components: sInput as String, rFind, and rReplace as Range. Then, we’re setting 7 variables. After that, we’re counting the rows and columns using the Count method.

Here, using the IF NOT statement to check  if the column lengths and row lengths are not equal then an error will pop up. To show that error, We’re using the CVErr function here. CVErr(xlErrNA) will return the #NA error.

After that, we’re using a For loop. For each value in the rFind range, we’re replacing it with the sReplace values.

• Save the code and go back to your worksheet.

• After that, select the cell range D5:D10 and type the following formula.

We’re replacing the value from cell C5. The values to be replaced are in the F5:F7 cell range. The new values are in the G5:G7 cell range.

• Finally, press CTRL + ENTER.

We’ve changed the values using a custom VBA formula.

Read More: Excel VBA: How to Find and Replace Text in Word Document

Things to Remember

• Use the absolute cell references where it is necessary.
• Method 3,4,6 requires one more extra step to replace multiple words.
• The XLOOKUP function is only available on Excel 365, and Office 2021.

Practice Section

We’ve added a practice dataset for each method in the Excel file. You can try our method and understand more clearly with those.

Conclusion

We’ve shown you 7 methods to find and replace multiple words at once in Excel. If you have any problems regarding these methods, feel free to comment below for assistance. Thanks for reading, keep excelling!

Related Articles

• How to Substitute Multiple Characters in Excel (6 Ways)
• Excel VBA to Find and Replace Text in a Column (2 Examples)
• [Fixed!] Excel Find and Replace Not Working (6 Solutions)
• Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)
• How to Find and Replace Using Formula in Excel (4 Examples)