Merge and unmerge cells

Important notice for users of Office 2003    To continue receiving security updates for Office, make sure you're running Office 2003 Service Pack 3 (SP3). The support for Office 2003 ends April 8, 2014. If you’re running Office 2003 after support ends, to receive all important security updates for Office, you need to upgrade to a later version such as Office 365 or Office 2013. For more information, see Support is ending for Office 2003.

You can merge two or more adjacent cells into one cell and display the contents of one cell in the merged cell (merged cell: A single cell that is created by combining two or more selected cells. The cell reference for a merged cell is the upper-left cell in the original selected range.). You can also merge the contents of several cells and display them in one cell. Merging cells disables sorting functionality.

 Important   You can unmerge (or split) a cell that has been merged, but you cannot split an empty, unmerged cell. You can, however, split the contents of an unmerged cell and distribute the divided contents across other cells.

What do you want to do?

Merge cells

Split merged cells

Merge the contents of multiple cells into one cell

Split the contents of cells across multiple cells

Merge cells

When you merge two or more adjacent cells, the cells become one merged cell, and the contents of the upper-left cell are displayed in the center of the merged cell, as shown in the following example.

Text spread and centered over multiple cells

Text spread and centered over multiple cells

 Important   Only the data in the upper-left cell of a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of selected cells will remain in the merged cell. Data in other cells of the selected range will be deleted.

  1. If the data that you want to display in the merged cell is not in the upper-left cell, do the following:
    1. Select the data that you want to display in the merged cell, and then click Copy Button image on the Standard toolbar.
    2. Select the upper-left cell of the range of adjacent cells that you want to merge, and then click Paste Button image on the Standard toolbar.
  2. Select the cells that you want to merge.

 Note   The cells that you select must be adjacent.

  1. On the Formatting toolbar (toolbar: A bar with buttons and options that you can use to carry out commands. To display a toolbar, point to Toolbars on the View menu. If you don't see the button you want, click the arrows at the right end of the toolbar.), click Merge and Center Button image.

The cells will be merged in a row or column, and the cell contents will be centered in the merged cell.

 Note   If the Merge and Center Button image button is unavailable, the selected cell may be in editing mode. To cancel editing mode, press ENTER.

  1. To change the text alignment in the merged cell, select the cell, and then click Align Left Button image or Align Right Button image on the Formatting toolbar.

Top of Page Top of Page

Split merged cells

You can split only cells that were previously merged.

  1. Select the merged cell.

When you select a merged cell, the Merge and Center button Button image also appears selected (selected: A toolbar button always has a border around it when it's selected, even when the pointer is not resting on the button.) on the Formatting toolbar.

  1. To unmerge cells, click Merge and Center Button image.

 Note   When the merged cell is split, the contents of the merged cell will appear in the upper-left cell of the range of split cells.

Top of Page Top of Page

Merge the contents of multiple cells into one cell

You can use a formula with the ampersand (&) operator to combine text from multiple cells into one cell.

  1. Select the cell in which you want to combine the contents of other cells.
  2. To start the formula, type =(
  3. Select the first cell that contains the text that you want to combine, type &" "& (with a space between the quotation marks), and then select the next cell that contains the text that you want to combine.

To combine the contents of more than two cells, continue selecting cells, making sure to type &" "& between selections. If you don't want to add a space between combined text, type & instead of &" "&. To insert a comma, type &", "& (with a comma followed by a space between the quotation marks).

  1. To finalize the formula, type )
  2. To see the results of the formula, press ENTER.

Tip    You can also use the CONCATENATE function to combine text from multiple cells into one cell.

Example

The following example worksheet shows the available formulas that you can use. The example may be easier to understand if you copy it to a blank worksheet.

ShowCopy the example to a blank worksheet

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headings.

Selecting example from Help

Selecting an example from Help
  1. Press CTRL+C.
  2. On the worksheet, select cell A1 and then press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
1
2
3
A B
First Name Last Name
Nancy Davolio
Andrew Fuller
Formula Description (Result)
=A2&" "&B2 Combines the names above, separated by a space (Nancy Davolio)
=B3&", "&A3 Combines the names above, separated by a comma (Fuller, Andrew)
=CONCATENATE(A2," ",B2) Combines the names above, separated by a space (Nancy Davolio)

 Note   The formula inserts a space between the first and last names by using a space enclosed within quotation marks. Use quotation marks to include any literal text — text that does not change — in the result.

Top of Page Top of Page

Split the contents of cells across multiple cells

You cannot split a cell or range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells that was not previously merged. You can, however, divide the contents of unmerged cells and display them across other cells.

  1. Select the cell, the range of cells, or the entire column that contains the text values that you want to divide across other cells. A range can be any number of rows tall, but no more than one column wide.

 Important   Unless there are one or more blank columns to the right of the selected column, the data to the right of the selected column will be overwritten.

  1. On the Data menu, click Text to Columns.
  2. Follow the instructions in the Convert Text to Columns Wizard to specify how you want to divide the text into columns.

 Note   For help with completing all the steps of the wizard, click Help Button image in the Convert to Text Columns Wizard.

Top of Page Top of Page

 
 
Applies to:
Excel 2003