How to use Range.Replace in the xlwings API way

The Replace method of the Range object in xlwings is a powerful tool for performing text substitutions within a specified range of cells in an Excel worksheet. It functions similarly to the “Find and Replace” feature in the Excel user interface but is fully programmable, allowing for automation of data cleaning, formatting corrections, or bulk updates. This method searches for a specific string or pattern and replaces it with another string across all cells in the target range, offering control over case sensitivity, whole cell matching, and search direction.

Syntax in xlwings:

range_object.replace(what, replacement, lookat, search_order, match_case, match_byte, search_format, replace_format)

Parameters:

• what (str): The string to search for. This is the required primary argument.
• replacement (str): The string that will replace the found text.
• lookat (int or xlwings.constants): Optional. Specifies whether to match the entire cell content or part of it. Use xlwings.constants.LookAt.xlWhole (or 1) for entire cell, and xlwings.constants.LookAt.xlPart (or 2) for partial matches. Default is xlPart.
• search_order (int or xlwings.constants): Optional. Determines the search order: by rows (xlwings.constants.SearchOrder.xlByRows or 1) or by columns (xlwings.constants.SearchOrder.xlByColumns or 2). Default is xlByRows.
• match_case (bool): Optional. If True, the search is case-sensitive. Default is False.
• match_byte (bool): Optional. Primarily for double-byte character set languages. Default is False.
• search_format (bool): Optional. If True, the search includes cell formatting criteria. Requires setting formats via the Application object’s ReplaceFormat in advanced scenarios. Default is False.
• replace_format (bool): Optional. If True, the replacement applies formatting specified via ReplaceFormat. Default is False.

Code Examples:

1. Basic Text Replacement:
   Replace all instances of “old” with “new” in the range A1:C10, ignoring case.

import xlwings as xw
wb = xw.Book("workbook.xlsx")
ws = wb.sheets["Sheet1"]
ws.range("A1:C10").replace(what="old", replacement="new")

2. Case-Sensitive, Whole Cell Match:
   Replace only cells that exactly contain “Error” (case-sensitive) with “OK”.

from xlwings.constants import LookAt
ws.range("D1:D100").replace(what="Error", replacement="OK", lookat=LookAt.xlWhole, match_case=True)

3. Replacing Numbers and Using Search Order:
   Replace “2022” with “2023” in a range, searching by columns.

from xlwings.constants import SearchOrder
ws.range("F1:J50").replace(what="2022", replacement="2023", search_order=SearchOrder.xlByColumns)

4. Advanced Use with Formats (Conceptual):
   While direct format replacement is more complex and less commonly used in xlwings, it can be initiated by setting search_format or replace_format to True after defining format objects via the Excel application. This typically involves the api property for lower-level VBA-like calls.