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.

Find and Replace Multiple Words at Once


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 Multiple Words at Once

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.

Find and Replace Multiple Words at Once

We’ve found and replaced twoAL” 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.

Find and Replace Multiple Words at Once

Steps:

  • Firstly, select cell range D5:D10.
  • Secondly, type the following formula.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($C$5:$C$10,"AL","Alabama"),"IL","Illinois"),"MS","Mississippi")

Find and Replace Multiple Words at Once

Formula Breakdown

  • SUBSTITUTE($C$5:$C$10,”AL”,”Alabama”)
    • Output: {“Alabama”;”California”;”Delaware”;”IL”;”Texas”;”MS”}.
    • The SUBSTITUTE function replaces a string with another string. We’re replacing “AL” with “Alabama here.
  • SUBSTITUTE(SUBSTITUTE($C$5:$C$10,”AL”,”Alabama”),”IL”,”Illinois”)
    • We’re replacing “IL” , with “Illinois”.
  • Finally, SUBSTITUTE({“Alabama”;”California”;”Delaware”;”Illinois”;”Texas”;”MS”},”MS”,”Mississippi”)
    • We’re replacing “MS” with “Mississippi”.
  • Thirdly, press CTRL + ENTER.

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

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.
=XLOOKUP(C5,$F$5:$F$9,$G$5:$G$9,C5)

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 C5AL” is found on the lookup array, therefore, we’ll get “Alabama”.

Find and Replace Multiple Words at Once

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

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

Find and Replace Multiple Words at Once

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


Similar Readings:


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.
=IFERROR(VLOOKUP(C5,$F$5:$G$7,2,FALSE),C5) 

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.

Find and Replace Multiple Words at Once

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

Find and Replace Multiple Words at Once

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.
=REPLACE($C$5:$C$8,1,2,$F$5:$F$8)

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.

Find and Replace Multiple Words at Once

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

Find and Replace Multiple Words at Once

Steps:

  • Firstly, type the formula from below to cell D5.
=IF(LEN(C5)=2,REPLACE(C5,1,2,IF(C5="AL","Alabama",IF(C5="IL","Illinois",IF(C5="MS","Missisippi")))),C5)

Formula Breakdown

See method 5, to understand the syntax of the REPLACE function.

  • LEN(C5)=2
    • The LEN function returns the string length of a cell. Our short form consists of 2 characters. Hence, we’re checking if it is equal to 2. If it is TRUE, then we’ll replace it with the REPLACE function. On the other hand, if it is not TRUE then the cell value will be returned.

For the value in cell C5. We can see its length is 2. Hence, it will execute the REPLACE function. This IF(C5=”AL”,”Alabama” part will change “AL” to “Alabama”.

  • Secondly, press ENTER.

We can see the value is changed in cell D5.

  • Finally, AutoFill the formula.

Find and Replace Multiple Words at Once

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.

Find and Replace Multiple Words at Once

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.

Find and Replace Multiple Words at Once

  • After that, select the cell range D5:D10 and type the following formula.
=ReplaceWords(C5,$F$5:$F$7,$G$5:$G$7)

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.

Find and Replace Multiple Words at Once

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

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo