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

Creating Sample Excel File

Let's first create a sample Excel file to demonstrate word replacement ?

import openpyxl

# Create a new workbook and add sample data
wb = openpyxl.Workbook()
ws = wb.active

# Add sample data
ws['A1'] = 'apple'
ws['A2'] = 'banana'
ws['A3'] = 'orange'
ws['B1'] = 'red'
ws['B2'] = 'yellow'
ws['B3'] = 'orange'

# Save the file
wb.save('sample.xlsx')
print("Sample Excel file created with fruits and colors")

Sample Excel file created with fruits and colors

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 ?

import openpyxl

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

# Select the active worksheet
worksheet = workbook.active

print("Workbook loaded successfully")
print("Active sheet:", worksheet.title)

Workbook loaded successfully
Active sheet: Sheet

Replacing a Single 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 ?

import openpyxl

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

# Select the active worksheet
worksheet = workbook.active

# Replace the word 'orange' with 'mango'
for row in worksheet.iter_rows():
    for cell in row:
        if cell.value == 'orange':
            print(f"Word found in cell {cell.coordinate}")
            cell.value = 'mango'
            print(f"Word replaced in cell {cell.coordinate}")

# Save the changes
workbook.save('sample.xlsx')
print("Changes saved to Excel file")

Word found in cell A3
Word replaced in cell A3
Word found in cell B3
Word replaced in cell B3
Changes saved to Excel file

Replacing Multiple Words

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 ?

import openpyxl

# Create fresh sample data
wb = openpyxl.Workbook()
ws = wb.active
ws['A1'] = 'apple'
ws['A2'] = 'banana'
ws['A3'] = 'orange'
ws['B1'] = 'red'
ws['B2'] = 'yellow'
ws['B3'] = 'green'
wb.save('multi_replace.xlsx')

# Load the Excel spreadsheet
workbook = openpyxl.load_workbook('multi_replace.xlsx')
worksheet = workbook.active

# Define the words to be replaced and their replacements
replacements = {
    'apple': 'grape',
    'banana': 'kiwi',
    'red': 'blue'
}

# Replace the words
for row in worksheet.iter_rows():
    for cell in row:
        if cell.value in replacements:
            old_value = cell.value
            cell.value = replacements[cell.value]
            print(f"Replaced '{old_value}' with '{cell.value}' in cell {cell.coordinate}")

# Save the changes
workbook.save('multi_replace.xlsx')
print("Multiple word replacements completed")

Replaced 'apple' with 'grape' in cell A1
Replaced 'banana' with 'kiwi' in cell A2
Replaced 'red' with 'blue' in cell B1
Multiple word replacements completed

Replacing Partial Text

You can also replace partial text within cells using string methods ?

import openpyxl

# Create sample data with longer text
wb = openpyxl.Workbook()
ws = wb.active
ws['A1'] = 'The quick brown fox'
ws['A2'] = 'Python programming language'
ws['A3'] = 'Data analysis with pandas'
wb.save('partial_replace.xlsx')

# Load and process
workbook = openpyxl.load_workbook('partial_replace.xlsx')
worksheet = workbook.active

# Replace partial text
for row in worksheet.iter_rows():
    for cell in row:
        if cell.value and isinstance(cell.value, str):
            if 'Python' in cell.value:
                cell.value = cell.value.replace('Python', 'Java')
                print(f"Replaced 'Python' with 'Java' in cell {cell.coordinate}")
            elif 'fox' in cell.value:
                cell.value = cell.value.replace('fox', 'wolf')
                print(f"Replaced 'fox' with 'wolf' in cell {cell.coordinate}")

workbook.save('partial_replace.xlsx')
print("Partial text replacement completed")

Replaced 'fox' with 'wolf' in cell A1
Replaced 'Python' with 'Java' in cell A2
Partial text replacement completed

Comparison of Replacement Methods

Conclusion

The openpyxl library provides powerful functionality to replace words in Excel files using Python. You can replace single words, multiple words using dictionaries, or even partial text within cells. This automation saves time when working with large Excel datasets.