How to Find the Cell Addresses of All the Merged Cells in Google Sheets

In this tutorial, you are going to get a solution to find the cell addresses of all the merged cells in a table in Google Sheets.

I am going to use a regular expression in ‘Find and Replace’ menu command and then a formula to find the cell addresses of the merged cells.

But this novel idea has one drawback. The merged cells must contain values. That means the formula won’t return the cell address if the merged cell is blank.

Let’s start with the basic, which is merging and unmerging of cells in Google Sheets. After that, I’ll explain how to find cell addresses of the merged cells.

Merging | Unmerging Rows and Columns

I don’t prefer or recommend merging cells in Spreadsheets as it might affect some formulas especially database functions.

But sometimes we have no option left with other than merging cells, especially when making dashboard reports, formats like salary slips and some other formats.

You can merge vertically, horizontally or all together in Google Sheets. The relevant options are available under the Format menu.

You can possibly see the option ‘Merge cells’ grayed out under the Format menu. Select more than one cell to make this option enable.

Click on the Format menu item ‘Merge cells’ to see the said three different types of merging options available which are ‘Merge all’, ‘Merge horizontally’, and ‘Merge vertically’.

Vertically (Merging Rows)

Vertical merging is essentially for merging rows. For example, if you have values in the first row and want to merge it with the second row, use this method.

Here in the below example, I have selected the range B1:H5 and applied File > Merge > Merge vertically to merge five rows.

This is useful to merge header rows in a table.

Horizontally (Merging Columns)

If the values are in a column, use Merge horizontally to merge two or more adjoining columns. I hope the below screenshot is self explanting.

Merging All Cells in Google Sheets

The option ‘Merge all’ is for merging all the selected cells into one.

Our main topic is how to find the cell addresses of merged cells in Google Sheets. Before starting, one more point.

Unmerge Merged Cells in Google Sheets

To unmerge merged cells, first, select the merged cells and click on Format > Merge cells > Unmerge.

For Merge and Unmerge you can also use the corresponding tiny icons on Google Sheets shortcut bar.

Find the Merged Cells by Checking Cell Border in Google Sheets

In a small table, we can find the merged cells at a glance by applying the border.

Steps:

Select the range.
Go to the Help menu and search “Border” and select ‘Border type: All boarders’. This will apply borders to the selected range.
Now see the table, to visually identify the merged cells.

Find Merged Cells in a Small Table in Google Sheets

I know this is not a perfect method as using this method, it will be impossible to correctly find the merged cells in a large table in Google Sheets.

Here is my unique method that will help you find the cell addresses of all the merged cells in Google Sheets.

Please note that the below method has one drawback which I have already mentioned at the beginning, i.e the merged cells mustn’t be blank.

Find the Cell Addresses of the Merged Cells Using a Formula

Here again, I am using the above table for my test. Let me explain the logic first.

Enter the formula =E3 in cell J3 and =E4 in cell J4 (please note that the range E3:E4 is merged in the table).

The formula would return 25 in cell J3 and blank in cell J4. That means cell E4 has no value as it’s merged with cell E3. Do note this.

If we can extract the cell addresses of the blank cells from a range (table), that cell addresses will be cell addresses of the merged cells in that range (table).

But, there is one problem! if some of the unmerged cells are blank in the range, that cell addresses will also be treated as merged cells. Here is the solution/logic.

Logic:

To find the cell addresses of the merged cells in a table, first of all, replace all the blank cells other than merged cells with any value, for example, a hyphen. Then extract the cell addresses of the rest of the blank cells.

Step 1: Replacing Blank Cells with a Hyphen Except in Merged Cells

In an earlier tutorial, I have explained different methods to replace blank cells with 0 in Sheets. I am going to use one of the methods in that to fill blank cells in our table with a hyphen.

Select the range C2:G7 and go to Format > Number > Plain Text (we will restore the formatting later).
Click on ‘Find and Replace’ under the ‘Edit’ menu.
In the window that opens, apply the settings as per the image below and click on ‘Replace all’. This action will replace blank cells other than merged cells with - signs.

Regex: ^([\t]*)$

Step 1 - Replace Blank Cells with Hyphen

Select the range C2:G7 and apply Format > Number > Automatic.

Step 2: Formula to Find the Cell Addresses of All the Merged Cells in a Range

The above table (range) is within the tab named ‘Sheet1’. In ‘Sheet2’ which is blank, insert the below formula in cell C2.

=ArrayFormula(if(Sheet1!C2:G7="",address(row(Sheet1!C2:C7),column(Sheet1!C2:G2)),))

The formula will return the cell addresses of the merged cells in ‘Sheet2’ as below.

Cell Addresses of Merged Cells in Google Sheets

Similar Google Sheets Tips

1 COMMENT

Tito June 2, 2020 At 9:27 am

Thank you. Congratulations on finding merger cells. Kudos.

How to Find the Cell Addresses of the Merged Cells in Google Sheets