2

I am using Google Sheets and I am trying to find a way to identify merged cells without using a custom function.

For example, I need to determine (using only the built-in functions) whether any given cell has been merged with another.

The function, or chain or functions, that I would like to find would perform as follows:

  • If C4 has been merged with any other cell, =SOME_FUNCTIONS(C4)=TRUE
  • Otherwise, =SOME_FUNCTIONS(C4)=FALSE

Any thoughts?

(Edited for clarity)

0

5 Answers 5

3

Google Sheets doesn't have a built-in function that directly returns something to tell us if a cell is merged or not. Perhaps the closer function is CELL(info_type, reference) with the first parameter specifying the type of information that should be returned. Unfortunately it doesn't include an "info_type" for merged/un-merged.

On the other hand, it's a fact that a merged cell can't "store" any value/formula which in some cases could be helpful to figure out if a cell is merged or not.

Let see the following case:

  1. On Column A a series of consecutive numbers was written
  2. Cells A3 and A4 were merged
  3. On B1 there is a formula : =ArrayFormula (A:A)
    • Please note that B4 is empty.

On the above case we could use ISBLANK to know if a cell is merged. Replacing the above formula on B1 by =ArrayFormula(ISBLANK(A:A)) we get FALSE for the cells having a value and TRUE for the cells not having a value.

NOTE: If we replace the values of Column A by an array formula like =ArrayFormula(ROW(A:A)) the new formula on B1 will return TRUE for A4. Please note that the value is not stored on A4. An array of values is stored on A1 and displayed on A:A.

This isn't exactly what the OP is looking for but in some use cases something like this could be helpful.

1
  • if you have such an option you can use Conditional Formatting to check for merged cells where you can immediately spot merged cells where the color pattern is interrupted like:

    =MOD(COLUMN(),2)=0

    enter image description here


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

    =MOD(ROW(),2)=0

    enter image description here

0

Simple way to do this is to fill the unmerged cells with a dummy value and a formula such as IF(NE(C3, "*"), "✓", "x") to detect it. If you need the unmerged cells to contain a specific value, I'd recommend investigating the effect of =CELL("prefix", C3) which'll return the horizontal-alignment of a cell, but crucially, return nothing when that cell is anything but the top-left cell of a group of merged cells.

spreadsheet showing example

Share and enjoy

0

I found the best way to find merged cells in google sheets is to export the file to Microsoft Excel, and then use their function to find the merged cell (then go back to your original sheet and unmerge: https://support.microsoft.com/en-au/office/find-merged-cells-d02b2a5a-a08d-4641-8d4d-b3f233daca2c#:~:text=Click%20Home%20%3E%20Find%20%26%20Select%20%3E,merged%20cells%20in%20your%20worksheet.

In my giant spreadsheet, there were three merged cells.

-1

if you merge A1 with A2, anything you write in the merged cell will be in A1, therefore simple IF function should be enough to detect if the A2 cell is "merged" or not

try it:

  • type 123 in cell A1
  • merge cell A1 with cell A2
  • paste this into cell B1 =IF(A1=123;"merged";)
  • and paste this into cell B2 =IF(A2=123;"merged";)
  • to run test paste this into cell C1 =IF({B1;B2}="merged";"A1 is merged with A2";)
1
  • 1
    Sorry @user0, this is not a general enough solution. It will output A1 is merged with A2 if A1 and A2 independently have identical values, but are not merged,
    – amitrus
    Commented Aug 1, 2018 at 17:21

Not the answer you're looking for? Browse other questions tagged or ask your own question.