Normally when merging cells with the Merge feature in Excel, it will remove all cell contents except the first cell content. However, with Kutools for Excel 's Combine (Rows & Columns) utility, you can easily batch combine multiple cells/rows/columns without losing data. This will merge all the selected cells and the output will look like the below image. Method 2: Merging multiple cells using the Format Cells option. In this method, we are going to utilize the Format Cells option to merge the selected cells. First of all similar to the previous method, select the cells that you have to merge. Combine data using the CONCAT function Select the cell where you want to put the combined data. Select the cell you want to combine first. Use commas to separate the cells you are combining and use quotation marks to. Close the formula with a parenthesis and press Enter.

Content

Click one cell, then drag your mouse to select the other cell (s) you want to merge. For example, if you want to merge cells A1 through C1, you would click and drag from A1 right to C1. The cells you merge must be touching each other; for example, you can merge A1 with B1, but not with C1 without merging B1 as well.

Excel will give you a warning message telling that you will lose the data, when you try to merge cells using the Merge & Center feature. In this article, we’re going to show you how to merge cells in Excel without losing your data.

Merge
  1. Begin by adding a new column where you want the merged column to be placed.
  2. Select the first cell and create a formula CONCATENATE formula, adding a separator if necessary. In this example, we used a space (” “).
    =CONCATENATE(C2,” “,D2)
  1. Copy down the cell to apply to all cells in the column.
  2. When all cells in the new column are selected, copy the cells by pressing Ctrl + C. Alternatively, you can click the Copy item from the right-click menu.
  3. After copying, open the right-click menu again. This time click the Paste as Values button to replace formulas with static values.
  4. Since we got rid of the formulas, we do not need the old columns., and they can be safely removed. You can delete them by selecting the columns and clicking the Delete button from the right-click menu.
  5. You will end up with the merged column.
Usually, we merge cells in Excel to create a header or a label on the top of our worksheet or a table of data. Merging, often involves multiple columns, where we combine the data into one single cell. Now, what happens when we have data in rows, which we want to merge and combine into one cell? I’ll share with you a simple VBA macro (program) to merge and combine cells without losing data.

The image above reflects the output of the example that I have shared below. I have a row of data. Each cell has a value and I wish to merge all text and number values and show it as a single sentence in a cell on the top of my worksheet. You will not lose any data in the process.

Also Read:How to Split a Cells Value into Multiple Columns in Excel using VBA Split() Function

The Macro
Excel Merge Cells With Content

The program or the macro will execute when you click a button. Therefore, I’ll write the code in the button’s click event, in Sheet1. The button is an ActiveX control.

Now here's how you will merge all the cells value. First, select or highlight the rows that you wish to merge and then hit the button. See the image.

Excel Merge Cell Contents Separate Comma

In the macro, I am using the Selection object as a Range. The Selection object represents the selected area in the worksheet.

Here’s more on VBA Selection object.

Merge cells with content in excel

I’ll loop through the Selection object to extract each cells value and concatenate the texts and numbers (if any) and store it in a variable.

Finally, I’ll use the Selection object again, to assign the extracted values to a cell (Cells(1) or the first cell in the first row), align it according to my requirement and merge it. I have set WrapText as True.

Merge Cells using Excel’s Justify Feature

If you are not comfortable with VBA programming or macros, then you can use Excel’s Justify feature.

Inside the Home tab (in your Excel worksheet) look for the Fill option. It’s a dropdown. See the image.

However, before using the option, you’ll need to first increase the width of the column. Else, it will not give you the desired result.

Combining 3 Cells In Excel

Remember, the column should be wide enough to write or merge all the texts in one cell.

The Excel Justify feature however, comes with some limitations. It does not work on cells with numbers and formulas.

Related Post:How to Merge multiple rows in one Cell repeatedly in Excel

While explaining about merging using VBA macro in the first example above, I have added a number (in the 6th row). While the VBA code works fine, and will merge the numbers along with texts, applying the Justify feature will throw an error (in case a cell has numbers or formulas).

Therefore, get rid of the number (or convert the number to text by adding a single quote ' to merge the cells into one. I personally prefer the first example (using VBA code).

Excel Merge Cells With Content Formula

Merge

Merge Cells And Keep Content

← PreviousNext →

Excel Combine Cells With Content


Coments are closed

Most Viewed Posts

  • Best Indian Movies On Amazon Prime
  • Using Manycam
  • Suicide Squad Amazon Prime
  • Csv Merge Cells
  • Balance Discord

Scroll to top