[Comprehensive Guide] How to Combine Cells or Split Cells in Excel with Python

When working with Excel spreadsheets, it is common to merge cells to create table headers or make the layout more visually appealing and organized. Sometimes, however, you may decide that combining cells wasn't the right choice and need to split them again.

Whatever your needs, this article provides the solutions. We'll explain how to combine cells or split them in Excel using Python, enabling you to easily manage your Excel worksheets and data. Let’s check it out!

Prepare for Tasks

To merge cells in Excel with Python, we need to use Spire.XLS for Python. It is a powerful Python library capable of performing nearly all the operations that Microsoft Excel can. The key difference is that, with this tool and Python, you don't need to install Microsoft Office, and you can handle multiple tasks at once.

You can install Spire.XLS for Python from PyPI using the pip command below:

pip install Spire.XLS

How to Combine Cells in Excel with Python: Certain Range

On many occasions, combining a specific range of a worksheet improves the clarity and aesthetic of data. Whether you are preparing for a presentation or sharing data with clients, merging cells enables you to create a more cohesive layout. This part will introduce how to combine Excel cells in Python, providing detailed steps and a code example.

Steps to combine cells into one in Excel with Python:

• Create an object of the Workbook class and load the Excel document to be modified from the file path using the Workbook.LoadFromFile() method.

• Get a certain worksheet of the workbook with the Workbook.Worksheets[] method.

• Specify the cell range and combine Excel cells by calling the Worksheet.Range[].Merge() method.

• Store the resulting document with the Workbook.SaveToFile() method, and release the resources.

Here is the code example for combining two cells in Excel:

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


# Create an object of the Workbook class
workbook = Workbook()

# Load a sample Excel file from the disk
workbook.LoadFromFile("sample.xlsx")

# Get the 2nd worksheet of this file
sheet = workbook.Worksheets[1]

# Merge the particular cell range in Excel
sheet.Range["B3:C3"].Merge()

# Save the result file to the disk
workbook.SaveToFile("Combine_cellrange.xlsx", ExcelVersion.Version2013)

# Release the resources
workbook.Dispose()

How to Combine Excel Cells in Python: Rows or Columns

Speaking of combining Excel cells, you cannot miss merging rows or columns. Combining rows or columns has a wide range of applications in the workplace. For example, in financial statements, merging rows helps group different types of revenue under a single header. Similarly, merging columns can be useful in creating detailed schedules or timelines, where the merged cells serve as a visual cue to separate different phases or categories of tasks.

While merging Excel rows or columns manually takes a lot of time, the section will show you how to combine rows or columns in Excel with Python. Read on.

Steps to combine rows or columns in Excel:

• Create a Workbook object and open an existing document from the file using the Workbook.LoadFromFile() method.

• Get a certain worksheet with the Workbook.Worksheets[] property.

• Combine specific columns by calling the Worksheet.Columns[].Merge() method, and merge rows using the Worksheet.Rows[].Merge() method.

• Save the modified Excel document as a new file with the Workbook.SaveToFile() method, and release the resources.

Below is an example of combining the 1st row in the 2nd worksheet:

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


# Create an object of the Workbook class
workbook = Workbook()

# Load a sample Excel file from the disk
workbook.LoadFromFile("sample.xlsx")

# Get the 1st worksheet of this file
sheet = workbook.Worksheets[0]

# Merge the first column in Excel
# sheet.Columns[0].Merge()

# Merge the first row in Excel
sheet.Rows[0].Merge()

# Save the result file to the disk
workbook.SaveToFile("Combine_rowscolumns.xlsx", ExcelVersion.Version2013)

# Release the resources
workbook.Dispose()

How to Split Cells in Excel with Python: Specific Range

Cell splitting in Excel is often needed when a cell contains multiple pieces of information. For example, there is a cell that contains a customer’s name and address, and you want to sort the worksheet by name. Another case is that when you import data from other sources where information is concluded into a single cell, and you need to separate them for analysis.

Don’t worry, the part will illustrate how to split cells in Excel with Python, meanwhile, you will learn a code example for a better understanding.

How to split one cell into two in Excel with Python:

• Instantiate an object of the Workbook class, and read the file using the Workbook.LoadFromFile() method.

• Retrieve the worksheet to be operated with the Workbook.Worksheets[] property.

• Split a cell in Excel into two by calling the Worksheet.Range[].Unmerge() method.

• Store the resulting Excel document on the disk with the Workbook.SaveToFile() method, and release the resources.

Here is the code example of splitting one cell into two in Excel:

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


# Create a Workbook object
workbook = Workbook()

# Load a sample Excel file from the file
workbook.LoadFromFile("sample.xlsx")

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

# Unmerge the particular cell range in Excel
sheet.Range["B3:C3"].UnMerge()

# Save the result file to the disk
workbook.SaveToFile("Split_cellrange.xlsx", ExcelVersion.Version2013)

# Release the resources
workbook.Dispose()

How to Unmerge Cells in Excel with Python: Rows or Columns

There are some times when you need to undo the merging because it is not appropriate or you need to add new data into the Excel worksheet. Splitting entire rows or columns helps restore flexibility in how data is displayed and managed, allowing for more precise control over the spreadsheet's content. In this section, you will learn how to unmerge a cell in Excel using Python.

Steps to split cells in Excel:

• Create a Workbook object, and use the Workbook.LoadFromFile() method to load the file.

• Get the specific worksheet from the workbook with the Workbook.Worksheets[] method.

• Unmerge the certain row with the Worksheet.Rows[].Unmerge() method, and split the certain column with the Worksheet.Columns[].Unmerge() method.

• Write the file into the disk with the Workbook.SaveToFile() method, and release the resources.

Below is a code example of splitting the 1st row in the 2nd worksheet:

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


# Create an object of the Workbook class
workbook = Workbook()

# Load a sample Excel file from the disk
workbook.LoadFromFile("sample.xlsx")

# Get the 2nd worksheet of this file
sheet = workbook.Worksheets[1]

# Unmerge the first column in Excel
# sheet.Columns[0].UnMerge()

# Unmerge the first row in Excel
sheet.Rows[0].UnMerge()

# Save the result file to the disk
workbook.SaveToFile("Split_rowscolumns.xlsx", ExcelVersion.Version2013)

# Release the resources
workbook.Dispose()

The Conclusion

This page illustrates how to combine Excel cells or split cells in Excel with Python. Each part contains comprehensive instructions and code examples for your reference. Having learned this guide, you can manage Excel documents more easily. We hope you find it useful!