How to replace a word in Excel using Python?

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

Install openpyxl

Before replacing Word in Excel, we need to use the Python package manager to install the openpyxl library in the system. To install openpyxl, enter the following command in the terminal or command prompt.

Pip install openpyxl

grammar

openpyxl.load_workbook(‘your_excel_file’)

Here, the openpyxl.load_workbook() function loads the Excel file from the system. Once the file is loaded, you can perform operations on the worksheet.

Load Excel Spreadsheet

To load an Excel table, we first need to import openpyxl, then use the load_workbook() function to load the spreadsheet, and use the workbook.active property to select the active table.

Example

The code to load the workbook is as follows:

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

Replace a word

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

Example

The code to replace old words with new words 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

Replace 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 be replaced.

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

in conclusion

In this article, we discussed how to replace words in Excel using Python’s openpyxl library. 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.

The above is the detailed content of How to replace a word in Excel using Python?. For more information, please follow other related articles on the PHP Chinese website!