Identifying a merged cell in Google Sheets without using a custom function

Question

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)

Answer 1

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.

Answer 2

• 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

  

---

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

  =MOD(ROW(),2)=0

  

Answer 3

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";)

Answer 4

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.

Share and enjoy