Search | Merged cells

This command selects all the merged cells which intersect the current selection.

•	Highlight a rectangular range of cells that includes some merged cells, and the selection will automatically be enlarged so that it includes the merged area. It is impossible to select a smaller area.

•	You can select an entire column or row by clicking on its header. But if the column or row intersects any merged cells, those cells get selected too. That can be tiresome; in particular, it stops the Insert rows and Insert columns commands from working (see Chapter 10 for a way around this).

•

You can select an entire column or row with the keyboard: Ctrl+Space selects the column, Shift+Space the row. But if the column or row intersects any merged cells, those cells get selected too, along with the entire columns or rows that they in turn intersect. In most circumstances that renders the ability to select rows and columns with the keyboard unusable. This is particularly confusing if the merged cells happen to be hidden.

•	Text that won't fit in a cell is usually able to spill out into its neighbors, so long as they are unoccupied. In a merged cell, however, the text is truncated.

•	Unlike every other cell formatting option, the merging of cells is not simply a characteristic of a format style; it actually changes the regular grid structure of your spreadsheet.

A common use of the Excel Merge and Center command is to center heading text above a number of columns over which the heading applies. The OAK4 | Search | Merged cells command allows you to find any merged cells that exist on your worksheet. Once identified, the merged cells can then be reverted back into individual cells and formatted as follows to achieve the same appearance of text within merged cells:

•	open the Format Cells dialog box either by right-clicking the mouse on an active merged cell or by using Ctrl+O,E;

Either select the range of cells you suspect to contain merged cells or select just a single cell to find all the merged cells on the worksheet. Then choose the OAK4 | Search | Merged cells command; it will either select those merged cells that intersect the current selection or return a “No merged cells found in selection” message.

If the active cell happens to be a merged cell, then the OAK4 | Search | Merged cells command will only identify this cell, ignoring other merged cells within the worksheet. In this case, the One-Cell Rule does not apply because the active cell is being treated as a selection of more than one item as opposed to a single cell.

There are no comparable tools within Excel capable of locating multiple merged cells in a worksheet. A probable solution would be to implement a search algorithm in a macro. The identified merged cells can then be split apart using the “Center Across Selection” Horizontal Text alignment formatting option described above.