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.
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.
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 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.
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.
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.
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.
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
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).
← PreviousNext →