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 in the merged cell. You can also combine the content of several cells and display the combined content in one 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:
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.
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.
Select the range of cells that you want to merge.
Note: The cells that you select must be adjacent.
On the Home tab, under Alignment, click Merge.
Split 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.
Select the merged cell.
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.
Combine the text of multiple cells into one cell
To combine text from multiple cells into one cell, use the join (&) operator.
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.
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).
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).
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.
Divide 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.
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.
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.
On the Data tab, under Tools, click Text to Columns.
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.