How to replace a word in excel using Python?
Excel is a very useful tool where we can have the data in the format of rows and columns. We can say that before the database comes into existence, excel played an important role in the storage of data. Nowadays using Excel input, many batch processing is getting done. There may be the requirement of replacing text in Excel sheet is always there as excel always holds important data. In this article, let us see how to replace a word in Excel using Python
Methods and approaches used
Here let us see via xlwt & xlrd packages and openpyxl packages for replacing a word in excel using Python
Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.
To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning - Basic Level Course
Method 1 : xlwt & xlrd
To install these packages type the below command in the terminal.
# for writing into excel pip install xlwt # for reading pip install xlrd
The Below code has two excel workbook. One for reading and getting the text. Other for writing the replaced text. We can do “n” number of replacements. The column should exactly have the specified searched text which needs to be replaced and once found they are replaced and written to a new workbook.
Excel file used –
Method 2 : openpyxl
To install this module type the below command in the terminal.
pip install openpyxl
The advantage of openpyxl package is it can be used to both read and write xlsx/xlsm/xltx/xltm files. Below code uses openpyxl in reading and getting the text from one excel file, replacing the text and writing into another excel file.
Excel File Used –
Note: openpyxl does not support the old .xls file format, use xlrd to read this file, or convert it to the more recent .xlsx file format