How to merge and split cells in Excel and Calc?

Merging and splitting cells in Microsoft Excel and Sun OpenOffice Calc are very similar. Below are the steps required for merging and splitting cells in each of these programs.

Merge cells

Note: This document only covers merging the cells in excel, not how to merge the values of more than one cell.

  1. Open Calc or Excel.
  2. Highlight two or more adjacent cells you want to merge. For example, highlight cells A1 and B1 or C1 and C2.
  3. Click the Merge cells button in the formatting bar. Below is an example of what this button looks like in each of the versions of Excel and Calc that support this option.

Merge / Split icons in Calc and Excel

In Microsoft Excel 2010 and above, the Merged cell feature can be found under the Home tab and is shown in the picture below.

Office 2010 merged cell button

Note: When cells with multiple values are merged, the upper-left most cell will be the data of the merged cell.

Merge two cells using a formula

In Excel, you can merge two cells using a formula. Using the ampersand (&) in a formula acts to combine two cells or a cell and other text together.

Merge Cells in Excel

In this example, the formula =A1&B1 combines the values in cells A1 and B1 into cell C1. In this same example, if you wanted a space between the two cell values, the formula would include a "space" in it and be written as =A1&" "&B1. You would use double quotes around a space to add a space in the formula and the resulting merged value.

Split cells

  1. Open Calc or Excel.
  2. Highlight the merged cell you want to split. A merged cell will expand into multiple columns or rows.
  3. Click the Merge cells button in the formatting bar. Below is an example of what this button looks like in each of the versions of Excel and Calc that support this option.

Merge / Split icons in Calc and Excel

Microsoft Excel 2000 users

Unfortunately, splitting a cell in Excel 2000 is not as easy as clicking the merge button as explained above (this was introduced in later versions of Excel). To split a cell in Excel 2000, follow the steps below.

  1. Highlight the merged cell you want to split. A merged cell will expand into multiple columns or rows.
  2. Click the Format drop-down-menu.
  3. Click the Alignment tab.
  4. Within the Alignment tab, uncheck the Merge cells check box.

Split unmerged cell using a formula

In Excel, you can also split an unmerged cell using the Text to Columns option.

  1. Select the cells you want to split into two cells.
  2. On the Data tab, click the Text to Columns option.
  3. In the Convert Text to Columns Wizard, if you want to split the text in the cells based on a comma, space, or other character, select the Delimited option. If you want to split the text based on a specific number of character length, select the Fixed width option. Then click the Next button.
  4. For the Delimited option, select the character you want to split on. For the Fixed width option, select where you want to split the text by clicking in the Data preview section of the Wizard window. Then click the Next button.
    Text to Columns - Delimiters      Text to Columns - Fixed width
  5. In the last step, select any specific text formatting options for the split text. Then click the Finish button.

Additional information

  • See the cell, merge, and split definitions for further information and related links.