Python: Find and Replace Data in Excel

The Find and Replace feature in Excel allows you to quickly find specific values and perform targeted replacements based on specific requirements. With it, all occurrences of a specific value can be updated at once, which can significantly improve productivity when working with large data sets. In this article, you will learn how to programmatically find and replace data in Excel in Python using Spire.XLS for Python.

• Find and Replace Data in a Worksheet in Excel
• Find and Replace Data in a Specific Cell Range in Excel

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Find and Replace Data in an Excel Worksheet in Python

Spire.XLS for Python offers the Worksheet.FindAllString() method to find the cells containing specific data values in an Excel worksheet. Once the cells are found, you can use the CellRange.Text property to update their values with new values. The detailed steps are as follows:

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[] property.
• Find the cells containing a specific value in the worksheet using Worksheet.FindAllString() method.
• Iterate through the found cells.
• Replace the value of each found cell with another value using CellRange.Text property.
• Set a background color to highlight the cell using CellRange.Style.Color property.
• Save the result file using Workbook.SaveToFile() method.

from spire.common import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Load an Excel document from disk
workbook.LoadFromFile("input.xlsx")

# Get the first worksheet
worksheet = workbook.Worksheets[0]

# Find the cells with the specific string value “Total” in the worksheet
ranges = worksheet.FindAllString("Total", False, False)

# Iterate through the found cells
for range in ranges:
    
    # Replace the value of the cell with another value
    range.Text = "Sum"

    # Set a background color for the cell
    range.Style.Color = Color.get_Yellow()
    
# Save the result file
workbook.SaveToFile("FindAndReplaceData.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Find and Replace Data in a Specific Cell Range in Excel in Python

Spire.XLS for Python also allows you to find the cells containing a specific value in a cell range through the CellRange.FindAllString() method. Then you can update the value of each found cell with another value using the CellRange.Text property. The detailed steps are as follows:

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[] property.
• Get a specific cell range of the worksheet using Worksheet.Range[] property.
• Find the cells with a specific value in the cell range using CellRange.FindAllString() method.
• Iterate through the found cells.
• Replace the value of each found cell with another value using CellRange.Text property.
• Set a background color to highlight the cell using CellRange.Style.Color property.
• Save the result file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Load an Excel document from disk
workbook.LoadFromFile("input.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Get a specific cell range
range = sheet.Range["A6:C13"]

# Find the cells with the specific value "Total" in the cell range
cells = range.FindAllString("Total", False, False)

# Iterate through the found cells
for cell in cells:

    # Replace the value of the cell with another value
    cell.Text = "Sum"

    # Set a background color for the cell
    cell.Style.Color = Color.get_Yellow()

# Save the result file
workbook.SaveToFile("ReplaceDataInCellRange.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.