Combine Data and Merge Cells in Excel

Combine cells contents and merge cells without losing data

Merging columns, rows or multiple cells in Excel has a number of approaches. ‘Merge & Center’ gives a neat and user-friendly look, but doesn’t keep all of cells contents. Using “&” or the CONCATENATE function helps combine contents into a string, but doesn’t merge cells and can be time consuming. How can you join data from large ranges and merge cells without losing data?

XLTools Combine Cells Data makes it easy:

  • Merge cells without losing data
  • Combine cells by columns, by rows, or into a single cell
  • Set any separator between combined values: space, semicolon, new line, etc.
  • Combine data using different separators in a string

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


Add XLTools add-in ‘Combine Cells Data’ to Excel 2016-2007

Works with: Microsoft Excel 2016, 2013, 2010, 2007, Office 365.

Download XLTools Add-in


Learn your way around:


Combine or merge cells – what is the difference

  • When you combine multiple cells, you join contents of these cells into one cell.
  • When you merge multiple cells, they become one larger merged cell.

XLTools lets you both merge cells and combine data – i.e. merge cells without losing data.

Please note: cells contents are a combined into a text string which uses formatted values, e.g. 10%, 12-MAR-2015, $40, etc. If some cells contain formulas, the string will use the computed result instead of the formula.


How to combine data from Excel columns

  1. Select two or more columns > Click ‘Combine Data’ on the XLTools tab.
  2. Select ‘Combine cells data by rows’.
  3. Select a suitable separator.
  4. Tick ‘Clear the contents of cells after combining’, if necessary.
    Compare the result on the screenshot with this option off and on.
  5. Click OK > Done. All data in columns are combined row by row.

How to combine data in Excel columns row by row


How to merge multiple Excel columns without losing data

  1. Select two or more columns > Click ‘Combine Data’ on the XLTools tab.
  2. Select ‘Combine cells data by rows’.
  3. Select a suitable separator.
  4. Tick ‘Merge cells after combining cells data’.
  5. Click OK > Done. Columns are merged row by row, while all data are safe and combined in the leftmost cells.

How to merge Excel columns row by row


How to combine data from Excel rows

  1. Select two or more rows > Click ‘Combine Data’ on the XLTools tab.
  2. Select ‘Combine cells data by columns’.
  3. Select a suitable separator.
  4. Tick ‘Clear the contents of cells after combining’, if necessary.
    Compare the result on the screenshot with this option off and on.
  5. Click OK > Done. All data in rows are combined column by column.

How to merge Excel columns row by row


How to merge multiple Excel rows without losing data

  1. Select two or more rows > Click ‘Combine Data’ on the XLTools tab.
  2. Select ‘Combine cells data by columns’.
  3. Select a suitable separator.
  4. Tick ‘Merge cells after combining cells data’.
  5. Click OK > Done. Rows are merged column by column, while all data are safe and combined in the top cells.

How to merge Excel rows column by column


How to combine data from a range into a single cell

  1. Select a range of adjacent cells > Click ‘Combine Data’ on the XLTools tab.
  2. Select ‘Combine cells data into a single cell’.
  3. Select a suitable separator.
  4. Tick ‘Clear the contents of cells after combining’, if necessary.
    Compare the result on the screenshot with this option off and on.
  5. Click OK > Done. All data are combined in a single cell.

How to combine data into a single cell


How to merge Excel cells into a single cell without losing data

  1. Select a range of adjacent cells that you want to merge > Click ‘Combine Data’ on the XLTools tab.
  2. Select ‘Combine cells data into a single cell’.
  3. Select a suitable separator.
  4. Tick ‘Merge cells after combining cells data’.
  5. Click OK > Done. The range is merged into a single cell, while all data are safe and combined in this cell.

How to merge cells into a single cell without losing data


How skip empty cells when combining and merging cells

If you primary range contains empty cells, it’s best to disregard them – and avoid blanks in the resulting string.

  1. Select a range > Click ‘Combine Data’ on the XLTools tab.
  2. Select separator and other options as usual.
  3. Tick ‘Skip empty cells’.
  4. Click OK > Done. The combined data do not contain blanks.

Compare the result with the ‘Skip empty cells’ option off and on:

How to skip empty cells when combining and merging cells


How to wrap text when combining and merging cells

After merging and combining large ranges, you may want to wrap text in cells to make it visible. To save time, set this option right away:

  1. Select a range > Click ‘Combine Data’ on the XLTools tab.
  2. Select separator and other options as usual.
  3. Tick ‘Wrap text’.
  4. Click OK > Done. The combined data are wrapped and visible.

How to set a custom separator when combining and merging cells

  1. Select a range > Click ‘Combine Data’ on the XLTools tab.
  2. Select: comma, comma and space, semicolon, semicolon and space, new line.
    Or, select ‘Other’ and type in any custom separator you want.
  3. Select other options as usual.
  4. Click OK > Done. The values in the combined data are delimited with the separator you have chosen.

How to apply different separators when combining and merging cells

When you have multiple columns, you may want to combine cells using different separators.

Simply break up the task into steps:

  • Step 1. Merge ‘First Name’ with ‘Last Name’:
  1. Select the columns > Click ‘Combine Data’ > Combine by rows.
  2. Select ‘space’ as a separator.
  3. Tick ‘Merge cells…’, ‘Clear contents…’, ‘Skip empty cells’.
  4. OK > Done, first and last names are combined into strings like ‘John Smith’.
  • Step 2. Merge the result with ‘Order ID’:
  1. Select the columns > Click ‘Combine Data’ > Combine by rows.
  2. Select ‘Other’ separator and type ‘: order #‘.
  3. Tick ‘Merge cells…’, ‘Clear contents…’, ‘Skip empty cells’.
  4. 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? Contact us or leave your comment below.

 

Leave a Reply

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