Quickly combine multiple columns or rows in Excel 2007/2010

Normally to combine columns or rows in Excel 2007/2010, only the contents of first column or row will not be cleared after combining. There is no option for you to do so, if you want to keep all the contents after combining. But with Kutools for Excel’s Combine Columns or Rows feature, you can quickly process following operations in Excel:

1. Combine contents from multiple columns (or rows) into one column (or row), and merge the combined cells.
For example: you can quickly combine the contents of column A, column B and column C into the column A, and the column B and column C will be merged. The combined contents can be separated with a specified separator. See example 3.

2. Combine contents from multiple columns (or rows) into one column (or row), and keep a copy of contents in combined cells.
For example: you can quickly combine the contents of column B and C into the column A, and the column B and C will not be merged, and their contents will be kept in the same place. The combined contents can be separated with a specified separator. See example 1.

3. Combine contents from multiple columns (or rows) into one column (or row), and delete the contents of combined cells.
For example: you can quickly combine the contents of column A, column B and column C into column A, and all the contents of column B and column C will be deleted. See example 2.

4. The combined contents can be separated with following separators: space, nothing (no separator), new lines, or input characters as separator.

kte-combine

Fig. 1 Combine Columns or Rows

Specify the separator for the combined contents in Data Separator:

"Space": Combined contents are separated with space.
"Nothing": No separator.
"New Lines": The combined content will be laid out in a new line after combining.
"Insert this text": Users can customize any text or characters to separate combined content. For example, users can input "&" to separated contents.

Use formatted Value

If the value in selected cell is "100", then users set cell format and change the "100" into "$100.00 USD", so the shown value in selected cell is "$100.00 USD". If users check this "Use Format Value" option, the merged content is "$100.00 USD"; if uncheck it, the merged content is "100'.

Usage:

Select contiguous columns or rows that you want to combine;
Get into Combine Columns or Rows dialog box, and select the proper options to combine, and click the "OK" button to finish combining.

Example 1: Combine contents from multiple columns into one column, and keep a copy of contents in combined cells.

Get into the "Combine Columns or Rows" dialog box, and check the "Keep contents of these columns" options and "Insert this text" separator.

"Keep contents of these columns" options: contents in selected columns will be added into the foremost column, while the other columns and their content are reserved.
"Insert this text" separator: users can customize any text or character to separate merged contents.

kte-combining-4

Fig 2: Example 1 - Combine Columns

Example 2: Combine contents of from multiple rows into row, and delete the contents of combined cells.

Get into the "Combine Columns or Rows" dialog box, and check the "Delete contents of combined cells" options and "Space" separator.

"Delete contents of combined cells" option: contents in selected rows will be added into the foremost row, and the other rows are reserved in blank.
"Space" separator: merged contents are separated with space.

kte-combining-rows

Fig 2: Example 2 - Combine Rows

Example 3: Combine contents from multiple columns into one column, and merge the combined cells.

Get into the "Combine Columns or Rows" dialog box, and check the "Merge the combined cells" option and "New lines" separator.

"Merge the combined cells" option: All selected columns will be merged into one Column.
"New lines" separator: merged content are show in lines.

kte-combining-5

Fig. 3: Example 3 - Combine Columns