Excel spreadsheets often contain thousands of records in multiple sheets. In such cases, finding and replacing a particular text manually could be a hectic task. Therefore, MS Excel provides the find and replace option to update the desired text with a single click. In this article, you will learn how to find and replace text in Excel files programmatically in Python.
- Python API to Find and Replace Text in Excel Files
- Find and Replace Text in Excel File
- Find and Replace Text in Excel using Regex
Python API to Find and Replace Text in Excel
Aspose.Cells for Python via Java is a powerful spreadsheet manipulation API that lets you create new and process existing Excel documents. The Excel automation features provided by the API also include finding and replacing the text seamlessly. You can install the API using the following pip command.
pip install aspose-cells
Find and Replace Text in Excel
The following are the steps to find and replace text in Excel files.
- Load the Excel file using the Workbook class.
- Create an object of ReplaceOptions class and set options such as case sensitivity and content matching.
- Replace the text in Excel workbook using Workbook.replace(searchTerm, replaceTerm, ReplaceOptions) method.
- Save the updated Excel file using Workbook.save(fileName) method.
The following code sample shows how to find and replace text in Excel using Python.
Find and Replace Text in Excel using Regex
You can also find and replace text in an Excel file that matches a particular pattern. The following steps show how to use a regular expression to find and replace text in an Excel file.
- Load the Excel file using the Workbook class.
- Create an object of ReplaceOptions class and set options such as case sensitivity and content matching.
- Indicate that the search term is a regular expression using ReplaceOptions.setRegexKey(True) method.
- Replace the text in Excel workbook using Workbook.replace(searchTerm, replaceTerm, ReplaceOptions) method.
- Save the updated Excel file using Workbook.save(fileName) method.
The following Python code sample shows how to search and replace text in Excel using regular expression.
Get a Free API License
You can use the API without evaluation limitations by requesting a free temporary license.
Conclusion
In this article, you have learned how to find and replace text in Excel files using Python. Furthermore, you have seen how to search text that matches a particular pattern in the Excel files. You can explore more about the Python spreadsheet API using the documentation. In case you would have any questions or queries, feel free to let us know via our forum.
