How to Merge and Unmerge Cells

by Chaknith Bin

Merging cells refers to the process of combining two or more adjacent cells into a single larger cell. Unmerging cells, on the other hand, is the opposite process where a merged cell is divided back into its original individual cells. This feature allows for flexibility, consistent alignment, and better data analysis.

IronXL enables the merging and unmerging of cells in a spreadsheet programmatically.



C# NuGet Library for Excel

Install with NuGet

Install-Package IronXL.Excel
or
Java PDF JAR

Download DLL

Download DLL

Manually install into your project

C# NuGet Library for Excel

Install with NuGet

Install-Package IronXL.Excel
or
Java PDF JAR

Download DLL

Download DLL

Manually install into your project

Start using IronPDF in your project today with a free trial.

First Step:
green arrow pointer

Check out IronXL on Nuget for quick installation and deployment. With over 8 million downloads, it's transforming Excel with C#.

C# NuGet Library for Excel nuget.org/packages/IronXL.Excel/
Install-Package IronXL.Excel

Consider installing the IronXL DLL directly. Download and manually install it for your project or GAC form: IronXL.zip

Manually install into your project

Download DLL

Merge Cells Example

The Merge method can be utilized to merge a range of cells. This process combines the cells without erasing any existing values or data, but only the value of the first cell in the merged region will be displayed. However, the values of the merged cells remain accessible in IronXL.

Please note
Merging cells within the filter range can cause conflicts in the Excel file, requiring to run Excel repair to view the Spreadsheet.

The code example below demonstrates how to merge a range of cells by specifying their addresses.

:path=/static-assets/excel/content-code-examples/how-to/csharp-excel-merge-cells-merge.cs
using IronXL;

WorkBook workBook = WorkBook.Load("sample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

var range = workSheet["B2:B5"];

// Merge cells B7 to E7
workSheet.Merge("B7:E7");

// Merge selected range
workSheet.Merge(range.RangeAddressAsString);

workBook.SaveAs("mergedCell.xlsx");
Imports IronXL

Private workBook As WorkBook = WorkBook.Load("sample.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

Private range = workSheet("B2:B5")

' Merge cells B7 to E7
workSheet.Merge("B7:E7")

' Merge selected range
workSheet.Merge(range.RangeAddressAsString)

workBook.SaveAs("mergedCell.xlsx")
VB   C#

Demonstration

Merge Cells Demonstration

Retrieve Merged Regions Example

Retrieving merged regions is a useful feature for identifying the displayed value in spreadsheet visualization software like Microsoft Excel. To obtain a list of merged regions, you can utilize the GetMergedRegions method.

:path=/static-assets/excel/content-code-examples/how-to/csharp-excel-merge-cells-retrieve-merged-regions.cs
using IronXL;
using System.Collections.Generic;
using System;

WorkBook workBook = WorkBook.Create();
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Apply merge
workSheet.Merge("B4:C4");
workSheet.Merge("A1:A4");
workSheet.Merge("A6:D9");

// Retrieve merged regions
List<IronXL.Range> retrieveMergedRegions = workSheet.GetMergedRegions();

foreach (IronXL.Range mergedRegion in retrieveMergedRegions)
{
    Console.WriteLine(mergedRegion.RangeAddressAsString);
}
IRON VB CONVERTER ERROR developers@ironsoftware.com
VB   C#

Unmerge Cells Example

Unmerging merged regions can be accomplished using two different approaches. The first and simplest method involves specifying the cell addresses, such as "B3:B6", to unmerge.

Alternatively, you can also unmerge cells based on the index of the merged region. The merged regions are listed in chronological order. To do this, you can retrieve the merged regions first and pass the desired index to the Unmerge method.

Please note
Cells address has to be of the exact merged region.

It is not possible to unmerge potions of the merged region.

:path=/static-assets/excel/content-code-examples/how-to/csharp-excel-merge-cells-unmerge.cs
using IronXL;

WorkBook workBook = WorkBook.Load("mergedCell.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Unmerge the merged region of B7 to E7
workSheet.Unmerge("B7:E7");

workBook.SaveAs("unmergedCell.xlsx");
Imports IronXL

Private workBook As WorkBook = WorkBook.Load("mergedCell.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Unmerge the merged region of B7 to E7
workSheet.Unmerge("B7:E7")

workBook.SaveAs("unmergedCell.xlsx")
VB   C#

Demonstration

Unmerge Cells Demonstration

Chaknith Bin

Software Engineer

Chaknith is the Sherlock Holmes of developers. It first occurred to him he might have a future in software engineering, when he was doing code challenges for fun. His focus is on IronXL and IronBarcode, but he takes pride in helping customers with every product. Chaknith leverages his knowledge from talking directly with customers, to help further improve the products themselves. His anecdotal feedback goes beyond Jira tickets and supports product development, documentation and marketing, to improve customer’s overall experience.When he isn’t in the office, he can be found learning about machine learning, coding and hiking.