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.

Steps:

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.

1
2
3
4
5
import openpyxl
from openpyxl.utils import get_column_letter
 
workbook = openpyxl.load_workbook('example.xlsx')
sheet = workbook.active

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.

1
2
3
4
5
cells_to_process = []
for row in sheet.iter_rows():
    for cell in row:
        if cell.value and isinstance(cell.value, str) and ',' in cell.value:
            cells_to_process.append(cell)

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
for cell in cells_to_process:
    values = cell.value.split(',')
    col_letter = get_column_letter(cell.column)
 
    # Shift cells below the current cell down
    sheet.move_range(
        "{}{}:{}{}".format(
            col_letter,
            cell.row + 1,
            col_letter,
            sheet.max_row
        ),
        rows=len(values) - 1
    )
 
    # Insert split values into the newly created space
    for i, value in enumerate(values):
        sheet['{}{}'.format(col_letter, cell.row + i)].value = value

Step 5: Save the updated workbook

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

pythonCopy codeworkbook.save('example_updated.xlsx')

Full Code:

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
import openpyxl
from openpyxl.utils import get_column_letter
 
# Step 1: Load the workbook and select the active sheet
workbook = openpyxl.load_workbook('example.xlsx')
sheet = workbook.active
 
# Step 2: Gather cells to process
cells_to_process = []
for row in sheet.iter_rows():
    for cell in row:
        if cell.value and isinstance(cell.value, str) and ',' in cell.value:
            cells_to_process.append(cell)
 
# Step 3: Process cells, split values, and shift cells below
for cell in cells_to_process:
    values = cell.value.split(',')
    col_letter = get_column_letter(cell.column)
 
    # Shift cells below the current cell down
    sheet.move_range(
        "{}{}:{}{}".format(
            col_letter,
            cell.row + 1,
            col_letter,
            sheet.max_row
        ),
        rows=len(values) - 1
    )
 
    # Insert split values into the newly created space
    for i, value in enumerate(values):
        sheet['{}{}'.format(col_letter, cell.row + i)].value = value
 
# Step 4: Save the updated workbook
workbook.save('example_updated.xlsx')

Result: