How To Split and Shift Cells in Excel using Python

Excel is a popular tool for data analysis, and Python is a powerful programming language that can be used to automate tasks in Excel.

One task that you may need to automate is splitting cells in Excel. In this tutorial, we will show you how to split a cell in Excel using Python.


Step 1. Create a file “example.xlsx” with the following contents:

Step 2: Load the Excel workbook and select the active sheet

First, import the required libraries and load the example Excel workbook (example.xlsx) using openpyxl. Then, select the active sheet.

Step 3: Gather cells to process

Iterate through the cells in the sheet and identify the ones that contain comma-separated values. Add these cells to a list for processing later.

Step 4: Process cells, split values, and shift cells below

For each cell in the list, split the comma-separated values and shift the cells below the current cell down to create space for the split values.

Step 5: Save the updated workbook

Save the updated workbook as a new Excel file (example_updated.xlsx).


Full Code:

Here’s the full code for splitting and shifting cells in an Excel file using Python and openpyxl: