top of page

How to Remove Merged Cells in Excel, and How Not to Remove Merged Cells

It's not uncommon when dealing with documents that have begun their existance in MS Word, and are then later added to Excel, to end up with merged cells . And sometimes people, annoying people, will deliberately merge cells in Excel. Anyone who isn't annoying knows this is bad - because then you can't sort the spreadsheet. There are different ways to get rid of merged cells - see the one proposed here:

. . . but don't follow Gordon's suggestion! You could end up with lost data. See this example where you start with several rows (Fig. 1), delete the results which come up in the 'Find' tool after a search for merged cells (Fig. 2), and end up with missing data. (Fig 3) In a spreadsheet which has hundreds of rows, you may miss this error. Instead make use of the tips from the last two nights to get rid of the merged cells and keep all of your data.

A. In this example, enter the VBA code in a module as shown here in the July 28 tip. Then enter the formula =StringConcat(" ",A1:C1) . . . which allows you to concatenate a range of cells. [Obviously this is best employed when you have a lot of columns, instead of just three as in this example.].

B. Now using the tip from the night of July 29, run the macro that allows you to delete the entries where an entire row has no data entered in it.

C. Run the macro and you'll end up with a revised spreadsheet in which the merged cells have been removed, and which you can sort. It may be necessary to re-size the rows to see all of the data.

bottom of page