Sven-Bo · July 1, 2023 06:41 · Jul 1, 2023
diff --git a/find_replace_excel_values.py b/find_replace_excel_values.py
@@ -0,0 +1,40 @@
+"""
+Title: Case-Insensitive Text Replacement in Excel Files
+
+Author: Sven Bosau
+Website: https://pythonandvba.com
+YouTube Channel: https://Youtube.com/@codingisfun
+
+This script leverages the openpyxl library to perform case-insensitive replacement of text in Excel files (.xlsx, .xls). 
+It traverses each cell in each worksheet of an Excel file, compares the lowercase content of the cell to the keys 
+of a predefined dictionary, and if a match is found, replaces the cell's content with the corresponding value. 
+
+The modified workbook is then saved in a dedicated output directory, ensuring all original casing of the text is 
+maintained in the replacements. This provides a handy solution when you need to uniformly replace text across 
+multiple Excel files irrespective of their case.
+
+Please make sure the openpyxl library is installed in your Python environment before executing the script. 
+You can install it using pip: `pip install openpyxl`.
+
+Don't forget to customize the input file path and replacement dictionary to align with your requirements.
+"""
+
+from pathlib import Path
+import openpyxl
+
+BASE_DIR = Path(__file__).parent
+INPUT_DIR = BASE_DIR / "Files"
+OUTPUT_DIR = BASE_DIR / "Output"
+OUTPUT_DIR.mkdir(parents=True, exist_ok=True)
+
+replacement_pair = {"small business": "Small Market", "midmarket": "Midsize Market"}
+
+files = list(INPUT_DIR.rglob("*.xls*"))
+for file in files:
+    wb = openpyxl.load_workbook(file)
+    for ws in wb.worksheets:
+        for row in ws.iter_rows():
+            for cell in row:
+                if isinstance(cell.value, str) and cell.value.lower() in replacement_pair.keys():
+                    cell.value = replacement_pair.get(cell.value.lower())
+    wb.save(OUTPUT_DIR / f"{file.stem}_NEW.xlsx")
No results found