Combine Data add-in: combine cells contents and merge cells without losing data

Merging columns, rows or multiple cells in Excel has a number of approaches. ‘Merge and Center’ gives a table a neat and user-friendly look, but keeps the contents only of the top left cell, deleting the rest. Using "&" operator or CONCATENATE function helps join cells' contents into one string, but doesn't merge cells and can be time consuming.

The Combine Data add-in helps merge cells in large table without losing data:

  • Combine cells values and merge cells at the same time
  • Combine cells by columns, by rows, or into a single cell
  • Set any separator between combined values: space, pipe, new line, etc.
  • Combine data using multiple different separators in one string

(Note: the video may not reflect the latest upgrades. Please use the guide below.)

 

Add "Combine Data" to Excel 2016, 2013, 2010, 2007

Works in: Microsoft Excel 2016 – 2007, desktop Office 365 (both 32 bit and 64 bit).

Download XLTools Add-in

Combine data ribbon button in Excel

 

Learn your way around:

Note: UNDO (undo the last operation) is not applicable. We recommend that you save backup copies of your files or use XLTools Version Control to keep track of changes.

How to combine and merge cells by columns, rows or into a single cell without losing data

 

Combine cells or merge cells in Excel – what is the difference

  • When you merge multiple cells, you create one larger merged cell in their place.
  • When you combine multiple cells, you join, or concatenate contents of these cells into one text string.

XLTools lets you both merge cells and combine data at the same time – in other words, the add-in lets you merge cells without losing data.

Note: the combined text string uses formatted values, e.g. 10%, 12-MAR-2015, $40, etc. If some cells contain formulas or references, the string will use their computed result.

 

How to merge cells into a single cell without losing data

  1. Select a range of adjacent cells to merge > Click the ‘Combine Data’ button on XLTools tab.
  2. Select 'Combine cells data: into a single cell'.
  3. Set a suitable separator.
  4. Check the box ‘Merge cells after combining cells data’.
  5. If necessary, check additional options:
    • 'Skip empty cells' if there are any in your range, to avoid blank values in the resulting text string.
    • 'Wrap text' to wrap text into multiple lines.
  6. Click OK > Done. The range is merged into a single cell, while all values are combined in this cell.

How to merge cells into a single cell without losing data

 

How to merge cells across rows without losing data (or merge columns)

  1. Select a range > Click the ‘Combine Data’ button on XLTools tab.
  2. Select 'Combine cells data: by rows'.
  3. Select a suitable separator.
  4. Check the box ‘Merge cells after combining cells data’.
  5. If necessary, check additional options:
    • 'Skip empty cells' if there are any in your range, to avoid blank values in the resulting text strings.
    • 'Wrap text' to wrap text into multiple lines.
  6. Click OK > Done. All cells are merged across rows, and all values are combined across rows.

How to merge Excel columns row by row

 

How to merge cells within columns without losing data (or merge rows)

  1. Select a range > Click the ‘Combine Data’ button on XLTools tab.
  2. Select 'Combine cells data: by columns'.
  3. Select a suitable separator.
  4. Check the box ‘Merge cells after combining cells data’.
  5. If necessary, check additional options:
    • 'Skip empty cells' if there are any in your range, to avoid blank values in the resulting text strings.
    • 'Wrap text' to wrap text into multiple lines.
  6. Click OK > Done. All cells within columns are merged, all values within columns are combined.

How to merge Excel rows column by column

 

How to combine data in a single cell (without merging cells)

Sometimes you may need to assemble values from multiple cells into one cell, without actually merging cells. Essentially, you'd want to copy values from each cell in the range and assemble them into one text string in single cell. You can do this in seconds with XLTools add-in:

  1. Select a range > Click the ‘Combine Data’ button on XLTools tab.
  2. Select 'Combine cells data: into a single cell'.
  3. Select a suitable separator.
  4. If necessary, check additional options:
    • ‘Clear the contents of cells after combining’ to remove values from source cells. Compare the result on the screenshot with this option off and on.
    • 'Skip empty cells' if there are any in your range, to avoid blank values in the resulting text string.
    • 'Wrap text' to wrap text into multiple lines.
  5. Click OK > Done. All values are combined in a single cell.

How to combine data into a single cell

 

How to combine Excel data across rows (or join multiple columns into one)

In some cases you may need to join multiple columns into one, without actually merging cells. Essentially, you'd want to combine values from these columns across rows. You can easily do this with the add-in:

  1. Select a range > Click the ‘Combine Data’ button on XLTools tab.
  2. Select 'Combine cells data: by rows'.
  3. Select a suitable separator.
  4. If necessary, check additional options:
    • ‘Clear the contents of cells after combining’ to remove values from source cells. Compare the result on the screenshot with this option off and on.
    • 'Skip empty cells' if there are any in your range, to avoid blank values in the resulting text strings.
    • 'Wrap text' to wrap text into multiple lines.
  5. Click OK > Done. All values are combined across rows in the left-most cells.

How to combine data in Excel columns row by row

 

How to combine Excel data within columns (or join multiple rows into one)

Sometimes you may need to join multiple rows into one, without actually merging cells. Essentially, you'd want to combine values from these cells by columns. You can easily do this with the add-in:

  1. Select a range > Click the ‘Combine Data’ button on XLTools tab.
  2. Select 'Combine cells data: by columns'.
  3. Select a suitable separator.
  4. If necessary, check additional options:
    • ‘Clear the contents of cells after combining’ to remove values from source cells. Compare the result on the screenshot with this option off and on.
    • 'Skip empty cells' if there are any in your range, to avoid blank values in the resulting text strings.
    • 'Wrap text' to wrap text into multiple lines.
  5. Click OK > Done. All values are combined within columns in the top-most cells.

How to merge Excel columns row by row

 

Why skip empty cells when combining and merging cells

If you source data contains empty cells, it's best to disregard them when combining and merging cells. This helps avoid blank values in the combined text strings. Compare the result with the 'Skip empty cells' option off and on:

How to skip empty cells when combining and merging cells

 

How to set a custom separator when combining and merging cells

XLTools ‘Combine Data’ add-in allows you to set any delimiter between values in a combined text string:

  • Comma
  • Comma and space
  • Semicolon
  • Semicolon and space
  • Space
  • New line
  • Or, select 'Other' and type in any custom separator you want, e.g. vertical pipe line

 

How to apply multiple different separators in one combined text string

Suppose, you have multiple columns, and you want to combine cells using different separators. Simply break the task into steps:

  • Step 1. Merge columns 'First Name' with 'Last Name':
  1. Select the columns > Click ‘Combine Data’.
  2. Select 'Combine cells data: by rows'.
  3. Set space as a separator.
  4. Check boxes 'Merge cells…', 'Clear contents…', 'Skip empty cells'.
  5. OK > Done, first and last names are combined into strings like "John Smith".
  • Step 2. Merge the result with the column 'Order ID':
  1. Select the columns > Click ‘Combine Data’.
  2. Select 'Combine cells data: by rows'.
  3. Set 'Other' separator and type ": order #".
  4. Check boxes 'Merge cells…', 'Clear contents…', 'Skip empty cells'.
  5. OK > Done, all data are combined into strings like "John Smith: order #123".

How to combine cells data using different separators

 

Any questions or suggestions? Please leave your comment below.

 

Leave a Reply

Your email address will not be published. Required fields are marked *