Merge or split cells or cell contents

Merging cells is useful to reformat a worksheet. You can merge two or more adjacent cells into one cell and display just the contents of the leftmost or uppermost cell from the original cell range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) in the merged cell (merged cell: A single cell 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 combine the content of several cells and display the combined content in one cell.

Example of text in a merged cell

Example of text in a merged cell

After you merge cells in a range, you may no longer be able to sort the range unless you unmerge the cells.

Do any of the following:

ShowMerge cells

 Important   Only the data in the upper-left cell from a range of selected cells remains in the merged cell. Excel deletes data in the other cells of the selected range.

  1. If the data that you want to display in the merged cell is not the data in the upper-left cell from the range that you want to merge, copy the data that you want to display to the upper-left cell.
  2. Select the range of cells that you want to merge.

 Note   The cells that you select must be adjacent.

  1. On the Home tab, under Alignment, click Merge.

ShowSplit merged cells

You can split only a merged cell. There are other methods to divide the content of a cell or range of cells across other cells.

  1. Select the merged cell.
  2. On the Home tab, under Alignment, point to Merge, and click Unmerge Cells.

The content of the merged cell will appear in the upper-left cell in the range of split cells.

ShowCombine the text of multiple cells into one cell

To combine text from multiple cells into one cell, use the join (&) operator (operator: A sign or symbol that specifies the kind of calculation — such as addition, subtraction, or multiplication — to be performed on the elements of a formula. Operators include mathematical operators; comparison, or logical, operators, which compare two values and produce the value TRUE or FALSE. The text operator & (ampersand), which concatenates two pieces of text into a single combined text value; and reference operators, which produce one reference to a cell or range of cells from two references.).

  1. On a blank sheet, type a list of first names in column A, and then type a corresponding list of last names in the adjacent cells in column B.

For example, in cell A1, type Jeff, and then in cell B1, type Price.

  1. In cell C1, type =A1&" "&B1.

The formula combines the names in cell A1 and cell B1 and separates them by using a space (Jeff Price).

  1. In cell D1, type =B1&", "&A1.

The formula combines the names in cell A1 and cell B1, putting the last name first and separating them by using a comma followed by a space (Price, Jeff).

 Notes 

  • You can use quotation marks (" ") to include any literal text — that is, text that does not change — in the result.
  • You can also use the CONCATENATE function to combine text from multiple cells into one cell. To learn more about this function, see CONCATENATE function.

ShowDivide the content of a cell or range of cells across other cells

Use this technique to separate first names from last names, or separate state names from addresses.

Divide the content of cells

 Note   This procedure is only for dividing the content of unmerged cells and distributing the divided content across other cells. It does not apply to splitting merged cells.

  1. Select the cell, the range of cells, or the column of cells that contains the text values that you want to divide across other cells.

Make sure that you have a blank cell to the right of the selected cells.

 Note   A range of cells can be any number of rows, but no more than one column.

  1. On the Data tab, under Tools, click Text to Columns.

Data tab, Tools group

  1. Follow the instructions in the Convert Text to Columns Wizard to specify how you want to divide the text into columns.

The first step in the Convert Text to Columns Wizard determines whether the data is delimited, that is, separated by commas, tabs, or other characters. For example, if your data is first and last names (as in the previous image), the Convert Text to Columns Wizard recognizes the data as delimited (instead of formatted as a fixed width). In the second step of the wizard you select the kind of character that separates the data. In the example image, the type of character is "space." The third step gives you a preview of how the data will appear after it is divided into columns.

 Important   Excel overwrites data to the right of the selected column unless there are one or more blank columns to the right of the selected column.

See also

Change column width or row height

Show or hide gridlines on a sheet

Freeze rows and columns

Apply, create, or remove a cell style

LEFT function

RIGHT function

MID function

 
 
Applies to:
Excel for Mac 2011