Wondering how to replace words in Excel? Keep reading!

Manually editing one or more words in text strings can be a hectic task, especially if there are many such edits are required. Also, when the edits span across multiple worksheets or workbooks, you’re easily looking at hours of lost time.

Instead, you can use different Excel functions, user interface commands, and automation tools to replace words in Excel with other words. Follow along with the methods described below along with their quick steps and illustrations to quickly update text strings in Excel.

Let’s explore these techniques below!

Using Flash Fill

The Excel Flash Fill tool is an excellent automation for minute editing purposes. However, the edits you do must follow a pattern since the Flash Fill tool analyzes the pattern in your actions on the worksheet and simply replicates those.

For instance, in the above dataset, you find that the site names for the employees are incorrect. You’d like to replace New York with California and vice versa. However, you’d like to keep the Site word common for all the cells in column C.

This is an editing task with a pattern. Therefore, it’s a good candidate for Flash Fill-based automation.

Manually edit C2 to Site California and C3 to Site New York.

Now, select C2 and C3.

Click on the Flash Fill or Fill Handle square at the bottom right corner of the selected cell range.

Now, drag this tiny square until the last cell where data exists and let go of the click.

Excel Flash Fill will automatically update these cells and replace the words according to your instructions.

When using this method, you must carefully select the edited cells in the vertical direction. If you highlight other columns along with the edited cells and drag them down, any numerical values on those columns will change, generating incorrect values.

For instance, in the above sample dataset, you edit H2 and H3 to replace words and select E2:H3 to apply Flash Fill.

Excel will change the values in the Pay column for the rest of the cells below G2.

So, you only need to select H2:H3 when dragging and not the whole horizontal cell range for the given dataset.

This Excel feature is only available in Excel 2016 and later editions. So, if you’re using an earlier Excel version, you won’t find this tool.

Using the Find and Replace Tool

The Find and Replace tool is the default Excel user interface command for replacing all or select instances of a word in Excel.

Besides replacing text, it allows you to look up source text using specific formats and apply new formatting to the replaced word. Furthermore, you can customize the look-up and replace actions by worksheet, or workbook, look up for texts in formulas, search by rows or columns, etc. Moreover, you can use the match case feature to replace only lower or upper-case words.

📒 Read More: 5 Ways To Change Text Case In Excel

This tool is available in most of the Excel editions. Hence, it’s a widely accepted and used standard for replacing a word in Excel with another.

Let me show you how you can replace all the instances of New York with California in the above dataset in one click using the Excel Find and Replace command.

Press Ctrl + H on the keyboard to bring up the Find and Replace dialog box.

Here’s how to configure the tool:

• Find what: Type in the word to be replaced in this field. If you want to remove a source word with specific formatting, use the dedicated Format button.
• Replace with: Enter the new word in this box. If you want to introduce new formatting for the replaced word, like font type, text color, etc., use the Format button on the right edge of the Replace with field.
• Keep Within, Search, and Look in in their default configurations. You can, however, click on these fields and customize them for specific requirements of word replacements, like in the whole workbook, in formulas, etc.
• Checkmark the Match case box if you want to replace words with a specific case, like upper or lower cases.

Now, hit the Replace All button to complete the word replacement process.

If there is more than one dataset and you’d like to apply the technique to a select dataset, highlight the dataset first and then use the Find and Replace tool.

The command will make changes in the selected cell range.

Using Text to Columns

If you have text strings of varying lengths and want to replace random words, for search engine optimization (SEO) or content creation, you can utilize the Text to Columns tool. It helps you to break the larger texts into individual words.

Then, you can process the output data in another tool or paraphrasing application to generate synonyms. Finally, you can use the TEXTJOIN function in Excel to combine the separate synonymous words into a cohesive text string.

To practice this method, select the source text strings and click on the Text to Columns command in the Data Tools block of the Data tab.

Click Next on the Convert Text to Columns Wizard dialog box.

On the next dialog box, select an appropriate delimiter to split text from options, like Semicolon, Tab, Space, etc., and click Next.

Select Text for the Column data format option and enter the destination cell reference inside the Destination field.

Click Finish to complete the process. Excel will separate the words and place those in adjacent columns.

You can now manually edit preferred words in the newly created dataset.

Now, select a cell where you want to combine these words into text strings.

Enter the following formula inside the cell and hit Enter:

=TEXTJOIN(" ",TRUE,B2,C2,D2,E2,F2,G2)

You only need to change the cell references in the above formula according to your own input dataset. Refer to the screenshot for clarification.

Excel will combine the separate words into a text string.

Now, drag down the fill handle of the first cell to join the rest of the words in your dataset into text strings.

Using the Excel SUBSTITUTE Function

The SUBSTITUTE function allows you to replace words with a formula. You can either type the required arguments directly in the formula syntax as texts in double quotes or use cell addresses as references for arguments.

Suppose, you’ve got a few text strings in which you need to remove one or more words and replace those with synonyms.

Organize your dataset as shown in the above screenshot.

Now, select D2 or the first cell where you want the modified text string and enter the following formula:

=SUBSTITUTE(A2,B2,C2,1)

In the above formula, A2 is the input text string, B2 is the target word to be replaced, C2 is the replaced word, and 1 is for the number of instances to be replaced. Customize the formula syntax according to your own dataset.

Now, hit Enter to calculate D2 or the cell you have selected.

You should see the updated text string.

Now, click on the fill handle and drag it down to replace words for the rest of the text strings.

Using the Power Query Tool

When importing a dataset to an Excel worksheet from an external database, you can use the Power Query to transform your data, like replacing incorrect or unusual words with the correct ones.

For instance, you’re importing your data from an Azure database. So, go to the Data tab and click on the Get Data command.

Hover the mouse cursor over the From Azure menu to open an overflow menu on the right side.

Now, select the appropriate Azure database source type from the context menu, like Azure SQL Database, Azure Table Storage, etc.

Your data will load into the Power Query Editor interface.

For this tutorial, I’ve imported the sample data from an Excel worksheet using the From Table/Range command in the Get & Transform Data block of the Excel Data tab.

If you’re doing the same, click OK on the Create Table dialog that shows up after clicking From Table/Range.

Now you should see your working data in Power Query.

Right-click on the column header of the source data to be modified.

Select Replace Values from the context menu.

You’ll now see the Replace Values wizard. Here’s how you need to configure this dialog box:

• Value To Find: Enter the word to be replaced, like New York in the current exercise.
• Replace With: Type in the new word you want in place of the old one.
• Advanced options: Click on the arrow to access more customizations, like Match entire cell contents and Replace using special characters.

Now, click OK to apply the changes you’ve made.

Power Query Editor will update the datasets according to your inputs.

Click on the File tab and choose Close & Load To from the context menu.

The Import Data dialog will show up. Select the Existing worksheet option and highlight the destination cell.

Click OK to import the transformed datasets from Power Query to an Excel worksheet. You can delete the old data table to reduce the Excel file size.

Replace Values works on multiple columns as well. However, you can replace only one word at a time.

Select multiple columns in Power Query, right-click, and choose Replace Values.

Enter the required values in the next dialog box that pops open.

Click OK so Power Query can replace the words you need.

Using the Excel VBA Editor

Do you want to learn how to replace words in Excel automatically with minimal manual inputs? Try this Excel automation involving Excel VBA.

I’ve outlined a few effortless VBA scripts so you can create Excel VBA macros to automate replacing words in text strings.

However, go through this Excel tutorial to learn the technique to transform a VBA script into a VBA macro:

📒 Read More: How To Use The VBA Code You Find Online

Basic Macro to Replace Words Automatically

You can use the following script freely to create a macro. The macro will walk you through the rest of the process for replacing a word in Excel:

Sub ReplaceWordsInRange()
    Dim sourceRange As Range
    Dim sourceWord As String
    Dim newWord As String
    Dim cell As Range
    
    ' Prompt user to select the range containing the source word
    On Error Resume Next
    Set sourceRange = Application.InputBox("Select the range containing the source word:", Type:=8)
    On Error GoTo 0
    
    If sourceRange Is Nothing Then
        MsgBox "No range selected. Exiting script."
        Exit Sub
    End If
    
    ' Prompt user to enter the source word
    sourceWord = InputBox("Enter the word you want to replace:")
    If sourceWord = "" Then
        MsgBox "No source word entered. Exiting script."
        Exit Sub
    End If
    
    ' Prompt user to enter the new word
    newWord = InputBox("Enter the new word to replace the source word:")
    If newWord = "" Then
        MsgBox "No new word entered. Exiting script."
        Exit Sub
    End If
    
    ' Replace the words in the selected range
    For Each cell In sourceRange
        If Not IsEmpty(cell.Value) Then
            cell.Value = Replace(cell.Value, sourceWord, newWord)
        End If
    Next cell
    
    ' Notify user that the task is complete
    MsgBox "Word replacement complete!"
End Sub

Once the macro is ready, press Alt + F8 to bring up the Macro dialog.

Select the ReplaceWordsInRange macro and hit Run.

Excel VBA Editor will show the following input boxes so you can interact with the script and instruct it to perform tasks:

• A clickable dialog box to type in the source data cell range or select the cell range using the mouse.

• Another input box asks you to type in the word to be replaced.

• The third dialog box is there for you to enter the new word.

• There will be a confirmation message box when the process is completed successfully.

Excel will show the updated dataset in the active worksheet.

Advanced Macro to Replace Words Automatically

The following script allows advanced modifications to one or many words in a text string with visual queues.

The VBA macro will scan the input text string, break up the words, show a numbered list, choose one or many numbers, enter replacement words, and finally, Excel makes the necessary updates.

Sub ReplaceWordsInText()
    Dim cell As Range
    Dim textString As String
    Dim words() As String
    Dim i As Integer
    Dim wordList As String
    Dim replaceWords As String
    Dim newWords As String
    Dim replaceWordsArray() As String
    Dim newWordsArray() As String
    
    ' Select the cell containing the text string
    On Error Resume Next
    Set cell = Application.InputBox("Select the cell containing the text string:", Type:=8)
    On Error GoTo 0
    
    If cell Is Nothing Then Exit Sub
    
    textString = cell.Value
    words = Split(textString, " ")
    
    ' Create a list of words with numbers
    For i = LBound(words) To UBound(words)
        wordList = wordList & (i + 1) & ". " & words(i) & vbCrLf
    Next i
    
    ' Input box to select words to replace
    replaceWords = InputBox("Enter the numbers of the words to replace, separated by commas:" & vbCrLf & wordList)
    If replaceWords = "" Then Exit Sub
    replaceWordsArray = Split(replaceWords, ",")
    
    ' Input box for new words
    newWords = InputBox("Enter the new words, separated by commas:")
    If newWords = "" Then Exit Sub
    newWordsArray = Split(newWords, ",")
    
    ' Replace words
    If UBound(replaceWordsArray) <> UBound(newWordsArray) Then
        MsgBox "The number of words to replace and new words must be the same."
        Exit Sub
    End If
    
    For i = LBound(replaceWordsArray) To UBound(replaceWordsArray)
        words(CInt(replaceWordsArray(i)) - 1) = newWordsArray(i)
    Next i
    
    ' Update the cell value
    cell.Value = Join(words, " ")
    
    ' Task completed message
    MsgBox "Task completed."
End Sub

When you execute this script, you get the following prompts:

• Select the input text string.

• Enter the numeric equivalence of the words separated by commas.

• Type in new words.

You’ll see a confirmation message when words are replaced successfully.

Conclusions

Now you know how to replace words in Excel using built-in functions, user interface buttons, Power Query, and Excel VBA-based automation.

Practice all or a few methods outlined above to select one that best suits your dataset.

Comment below if the article helped or if you want to share feedback.