How to replace a word in Excel using Python?

In Python, we can replace a word in Excel with another word using a third-party Python library called openpyxl. Microsoft Excel is a useful tool that is used for managing and analyzing data. Using Python we can automate some of the Excel data management tasks. In this article, we will understand how we can replace a word in Excel using Python.

Installing openpyxl

Before implementing the program to replace Word in Excel we need to install the openpyxl library in our system using the Python package manager. To install openpyxl type the following command on your terminal or command prompt.

Pip install openpyxl

Syntax

openpyxl.load_workbook(‘your_excel_file’)

Here, the openpyxl.load_workbook() function loads the Excel file from your system. After loading the file you can perform the operations on the sheet.

Loading an Excel Spreadsheet

To load an Excel sheet we need to import openpyxl first and then load the spreadsheet using the load_workbook() function and select the active sheet using workbook.active attribute.

Example

The code for loading the workbook is shown below −

import openpyxl

# Load the Excel spreadsheet
workbook = openpyxl.load_workbook('example.xlsx')

# Select the active worksheet
worksheet = workbook.active

Print("Workbook loaded")

Output

Workbook loaded

Replacing a word

To replace a specific word in Excel we need to iterate over each cell of the active Excel workbook and check if the word in the cell matches with the word we want to replace and then insert a new word in that cell.

Example

The code for replacing an old word with a new word is shown below.

import openpyxl

# Load the Excel spreadsheet
workbook = openpyxl.load_workbook('testing.xlsx')

# Select the active worksheet
worksheet = workbook.active

# Replace the word 'old_word' with 'new_word'
for row in worksheet.iter_rows():
   for cell in row:
      if cell.value == 'old_word':
         print("Word found")
         cell.value = 'new_word'
         print("word replaced")
            
      else:
         
# Save the changes
workbook.save('testing.xlsx')

Output

Word found
word replaced

Replacing Multiple Word

If we want to replace multiple words in an Excel spreadsheet, we can modify the previous code to use a dictionary instead of a single word. The keys in the dictionary represent the words to be replaced, and the values represent the words to replace them with.

Example

The following code demonstrates how to replace multiple words in an Excel spreadsheet −

import openpyxl

# Load the Excel spreadsheet
workbook = openpyxl.load_workbook('example.xlsx')

# Select the active worksheet
worksheet = workbook.active

# Define the words to be replaced and their replacements
replacements = {
   'old_word_1': 'new_word_1',
   'old_word_2': 'new_word_2',
   'old_word_3': 'new_word_3'
}

# Replace the words
for row in worksheet.iter_rows():
   for cell in row:
      if cell.value in replacements:
         print("Word found")
         cell.value = replacements[cell.value]
         print("word replaced")
        
      else:
         print("word not found")

# Save the changes
workbook.save('example.xlsx')

Output

Word found
word replaced

Conclusion

In this article, we discussed how we can replace a word in Excel using the openpyxl library of Python. The openpyxl provides functionality to open a spreadsheet workbook and iterate over the cells of the workbook. We can also replace multiple words in the spreadsheet as shown in one of the examples in this article.