Split merged cells and distribute contents

[eugene w]

I searched through the forums and could not find a solution.

I have a large spreadsheet of data with a mix of merged cells and individual cells (e.g. column A contains large merged cells (A2:A4999) indicating a primary location, column B contains smaller merged cells (B2:B299) indicating a sub-location, column C contains individual cells (C2) indicating equipment in that specific location). In order to make best use of the data (either sorting, filtering, or using pivots) I would like to un-merge each merged cell and distribute the text to the resulting split cells (e.g. each un-merged cell in column A would be filled with the primary location, etc.).

It seems like there should be a simple solution but it has me stumped for the moment.

Thanks in advance for your thoughts.

P.S. My example is a simplified version of the problem. The spreadsheet in front of me is very large and would be extremely time-consuming to un-merge each cell and use something like ctrl+enter to distribute the contents.

[shg]

1. Select the entire range containing merged cells and unmerge

2. Without changing the selection, do Edit > Goto > Special, Blanks

3. In the formula bar, type "=" (sans quotes), press the up arrow key, then Ctrl+Enter. This group-enters a reference to the cell above for all previously merged cells.

4. Do Edit > Copy, then Edit > Paste Special, Values.

Stop merging cells.

[shassouna]

I have a somewhat similar issue. I'm trying to fix a spreadsheet I inherited. It has a merged cell that I am trying to split but the "split" cell button is greyed out. Any idea what's causing this?
I've tried unhiding all the columns in the file to no avail.
thanks for any info.

[shg]

Please take a few minutes to read the forum rules, and then start your own thread.

Thanks.