How to find non-blank cell values "hidden" under merged regions?

[david.karr]

I have a large spreadsheet that uses merged regions extensively. I
wrote an application using the Java Jakarta POI framework that reads
this spreadsheet and generates other data from it. In general, it
works fine. However, I just discovered a curious anomaly in at least
a single merged region in the spreadsheet.

One merged region is 1 row and 2 columns. There is a single string
visible and centered in the merged region. I discovered that when my
application reads the cells, it finds that visible value when reading
the first cell, but it also finds a value in the second cell, which is
an obsolete value that we used to use.

I can verify this data is present if I select and Copy the entire
second column and paste it into an external editor. The hidden value
becomes visible.

I'm going to investigate whether I can detect this condition with POI,
but I'd like to know if there's some way in the Excel interface I can
detect and fix (remove) these hidden values.

 

[Jon Peltier]

That's strange. I've never retained a value from a cell that was merged with
another cell or cells above or left of it. I just tested in a new worksheet,
and the second value is lost upon merging, and in fact you have to approve a
dialog before the merge occurs if it will delete data.

- Jon

 

[Roger Govier]

Hi Jon

FYI you can retain data in a merged cell.
If you merge 2 cells with values, it is possible to get one value to
show, and for the other value to be retained but not visible.
David McRitchie unearthed this some while back whilst trying to resolve
a problem with a workbook from a poster to one of the NG's

This thread should give you the details
http://snipurl.com/1felt

 

[Jon Peltier]

Well, I had to try it myself before I would believe it. As I said before,
"That's strange." The values hidden behind the pseudo-merged cell are
included in formulas.

- Jon

 

[Jon Peltier]

This happens in Excel 97 through 2007.

- Jon

 

[Roger Govier]

Sorry Jon, I could have added that to save you testing.
At first I did not believe it, but when I tried it, then it behaved
exactly as Dave McRitchie described, in all versions of Excel.

 

[david.karr]

I have a large spreadsheet that uses merged regions extensively. I
wrote an application using the Java Jakarta POI framework that reads
this spreadsheet and generates other data from it. In general, it
works fine. However, I just discovered a curious anomaly in at least
a single merged region in the spreadsheet.

One merged region is 1 row and 2 columns. There is a single string
visible and centered in the merged region. I discovered that when my
application reads the cells, it finds that visible value when reading
the first cell, but it also finds a value in the second cell, which is
an obsolete value that we used to use.

I can verify this data is present if I select and Copy the entire
second column and paste it into an external editor. The hidden value
becomes visible.

I'm going to investigate whether I can detect this condition with POI,
but I'd like to know if there's some way in the Excel interface I can
detect and fix (remove) these hidden values.

(I thought I already wrote this here.)

If it matters, I was able to detect this condition in my application
using Jakarta POI, and at the same time I'm able to avoid the problem.
I'm now looking through all the merged regions, and all the cells in
each merged region. If there's a cell that's not the top left-hand
corner that has a non-null value, then that value will be "hidden",
and at that point, I can directly change the cell value to "null" for
that cell (although it doesn't write the file back out).

 

[Jon Peltier]

Good, at least you can defend against this issue.

- Jon