Merge or split cells or cell contents

CLICK HERE FOR THE SOURCE
Merge or split cells or cell contents

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.

Important You can split (or unmerge) 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.
3. 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 is unavailable, the selected cell may be in editing mode. To cancel editing mode, press ENTER.
4. 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.
2. 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).
4. To finalize the formula, type )
5. 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
3. Press CTRL+C.
4. On the worksheet, select cell A1 and then press CTRL+V.
5. 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.
2. On the Data menu, click Text to Columns.
3. 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.