Back
Excel

Merging and splitting cells or data

(First published in April 2011, this post remains very popular with customers.We thought others would like to know about it, too, so today we’re moving it back to the top slot on the Excel blog home page.) 

The title of this post sounds simple. Should be easy to do, right? But customers tell us that it’s very confusing and not at all clear where to find the features to get the results they want.

It all depends on what you want to merge or split. For example, you may want to create a large header cell by splitting the cells below it into a number of smaller cells. I often create such headers but use a different method than you’d expect because an individual cell simply cannot be split into smaller cells. Instead, I merge several cells into one larger cell above the cells for which it will be the header. The result is exactly the same.

Four cells merged into one cell that displays year 2010 above the four quarters

Other times, you might want to merge the contents of several cells into one cell. Thinking that you can just merge the cells, you quickly realize that only the contents of one cell will be displayed in a larger cell — and that’s not the result you want! Instead, you’ll need to use the CONCATENATE function to quickly combine the contents of two or more cells into one cell. Don’t let the fact that it’s a function intimidate you — it’s not as complicated as it sounds.

contents of 3 cells merged into cell D1using CONCATENATE function

As I mentioned earlier, there’s no way you can split a cell. But that doesn’t mean that you cannot split the contents of a cell and distribute the split portions into adjacent cells. I actually do that quite often by using the Text to Columns Wizard. It provides options for splitting the data which returns great results every time I use it.

 Text to Columns Wizard helps you split full name into first and last names.

The following table summarizes your options for merging and splitting cells or data in cells.

If you want to:

You can:

For more information, see:

Merge two or more cells into one large cell that displays the contents of the first cell

Select the cells, click the Merge and Center button, and then use the Alignment and Wrap text buttons to display the contents of the merged cells the way you want (Home tab, Alignment group).

Video: Merge cells or split merged data

Article: Merge and unmerge cells

Merge the contents of two or more cells into one single cell

Use the CONCATENATE function to combine the content of multiple cells.

Video: Combine the contents of multiple cells into one cell

Article: Combine two or more columns by using a function

Split the contents of cells to distribute it into adjacent cells

Use the Text to Columns Wizard to split the contents of cell across adjacent cells.

Video: Use the Text to Columns Wizard

Article: Distribute the contents of a cell into adjacent columns

 –Frederique Klitgaard

 More info on merging and splitting cells in Excel:

Split and merge data in URLs

Merge and unmerge cells

Video: Combine the contents of multiple cells into one cell 

Combine two or more columns by using a function

Video: Use the Text to Columns Wizard

Distribute the contents of a cell into adjacent columns

Notice: Array to string conversion in D:\home\site\wwwroot\wp-includes\taxonomy.php on line 3255 Notice: Array to string conversion in D:\home\site\wwwroot\wp-includes\category-template.php on line 1245

Tags