Generally, Excel enables you to combine data in two ways: you can either merge cells or concatenate their values. The first option means turning multiple cells into one. As a result, you get a single large cell that is displayed across multiple columns or rows. The gradebook was (sadly!) set up in a way that produced two different columns for each assignment: one for student group A and one for student group B. There isn't any overlapping data, but I need to combine the grades for each group into one 'master' column with all grades, regardless of group A or group B, for each individual assignment.
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 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.
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.
How To Combine Data In 2 Cells In Excel
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 →
Combine Cell Data Excel