How To Detect Which Cells Are Merged In Google Sheets

Question

While trying to remove duplicate phone numbers from a relatively large list I recently combined, Google Sheets presented me with this error message:

There was a problem. Your selection contains a merged cell. To remove duplicates, all cells must contain the same number of rows and columns.

The formula I used to test and try to expose merged cells within Google sheets was:

=if(columns(A2:F2) = 6, "True", "False")

But this always returned true Because even though the cells may be merged they are still considered individual cells.

I am wondering if there is an easy way to discover and sort out these cells in Google Sheets. Excel used to have a very simple way of doing it but has since removed the functionality.

Any ideas?

player0 · Accepted Answer · 2019-07-06T15:33:59.233

14

if you have such an option you can use Conditional Formatting to check for merged cells like:

=MOD(COLUMN(),2)=0

where you can immediately spot merged cells where the color pattern is interrupted

in the same manner you can use this formula for rows:

=MOD(ROW(),2)=0

or you can play with scripts to find merged cells: https://issuetracker.google.com/issues/36753230

edited Jul 06 '19 at 15:33

answered Jul 06 '19 at 15:28

player0

124,011
12
67
124

This solution worked but wasn't exactly efficient for a ton of data. Scripts may be the only answer for now, but I hope sheets will build this functionality in soon! – Ethan Jul 06 '19 at 15:37
@player0 Does the number 2 indicate the number of cells that have been merged? Is it possible to unmerge with a single formula? – TipVisor Jul 28 '21 at 01:00
1

@TipVisor no. number 2 indicates "every 2nd column" to be painted. unmerge with a single formula is not possible but there is a shortcut key that will reset all formatting on selected cells https://i.stack.imgur.com/6j0gO.png – player0 Jul 28 '21 at 07:45

score 1 · Answer 2 · answered May 22 '20 at 14:31

Custom formula

function isMerge(sheetName, a1Notation) {
  var range = SpreadsheetApp.getActive().getSheetByName(sheetName).getRange(a1Notation);

  var merges = [];
  for (var i = 0; i < range.getHeight(); i++)
  {
    var merge = range.offset(i, 0, 1, 1).isPartOfMerge();
    merges.push(merge);    
  }
  return merges;
}

Usage:

Paste the code above to the Editor: menu Tools > Script Editor...
Save Project
Use as usual formula: =isMerge(sheetName, a1Notation)

score 0 · Answer 3 · answered Feb 06 '21 at 15:20

you can copy and paste the column somewhere, for example creating an extra column next to it. You can then create a new column. You can run increasing numbers in both and subtract each cell from each other. If the result is not 0, then cells have been merged.

score 0 · Answer 4 · answered Mar 21 '22 at 10:05

I was facing a similar issue and found a hack - ctrl+shift+down button to select all the data in the column. This automatically became a selection of two columns (the one I want to select plus the one next to it). I then worked from the bottom of the list up, using ctrl+shift+down button to select all data in that column, starting from the bottom 20 entries - with this, only data in that column was select - it didn't automatically include the next row too, which meant that the merged cell was not within these cells.

I repeated this step, going up the rows ~50 rows at a time, until the selection suddenly became two rows instead, when I was actually just selecting a single row's data. This meant that the merged cell was somewhere in the last 50 rows, and I just went down the list within that selection to check for the merged cell.

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Mar 21 '22 at 11:46
This only works easily when each cell contains information. It's a pain for sparse columns — MERose, Apr 12 '23 at 10:10

How To Detect Which Cells Are Merged In Google Sheets

4 Answers4