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

Merging and Unmerging Rows and Columns

Before we dive into the solution, let’s quickly go over merging and unmerging cells in Google Sheets. While I don’t generally recommend merging cells, there are certain scenarios where it can be useful, such as creating dashboard reports or formatting salary slips.

To merge cells vertically, select the range you want to merge and go to the Format menu. You’ll find the Merge cells option, which allows you to merge all, merge horizontally, or merge vertically. This is particularly handy when you want to merge header rows in a table.

If you want to merge columns, use the Merge horizontally option. Simply select the desired column range and choose the Merge cells option under the Format menu.

Now that we’ve covered the basics of merging and unmerging cells in Google Sheets, let’s move on to finding the cell addresses of merged cells.

Find the Merged Cells by Checking Cell Border

In small tables, you can easily identify merged cells by applying borders. This method works by visually highlighting the merged cells. To do this:

Select the range you want to check.

Go to the Help menu and search for “Border”.

Select Border type: All borders. This will apply borders to the selected range.

Now, visually identify the merged cells based on the borders applied.

While this method is effective for small tables, it becomes virtually impossible to correctly identify merged cells in large tables. That’s why I have come up with a unique method that will help you find the cell addresses of all the merged cells in Google Sheets.

Find the Cell Addresses of the Merged Cells Using a Formula

To find the cell addresses of the merged cells in a table, follow these steps:

Replace Blank Cells with a Hyphen Except in Merged Cells:

Select the range where you want to find the merged cells.
Go to the Format menu and choose Number > Plain Text.
Click on Find and Replace under the Edit menu.
In the window that appears, enter the following regular expression in the Find field: ^([t]*)$.
Leave the Replace field blank.
Click on Replace all. This will replace any blank cells (other than merged cells) with hyphens.
Finally, select the range again and go to Format > Number > Automatic to restore the original formatting.

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

Open a new sheet (let’s call it “Sheet2”) and insert the following formula in cell C2:
```
=ArrayFormula(if(Sheet1!C2:G7="",address(row(Sheet1!C2:C7),column(Sheet1!C2:G2)),))
```
Note: Replace Sheet1!C2:G7 with the range you want to search for merged cells.
The formula will return the cell addresses of the merged cells in Sheet2.

That’s it! You have now successfully found the cell addresses of the merged cells in your Google Sheets table using a formula.

Please note that this method has one limitation: the merged cells mustn’t be blank. If some of the unmerged cells within the range are blank, those cell addresses will also be treated as merged cells. Keep this in mind while using this technique.

For more Google Sheets tips and tricks, visit Crawlan.com.

I hope this article has been helpful and that you can now easily find the cell addresses of merged cells in Google Sheets. Happy merging!