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.
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.
Option Explicit Private Sub CommandButton1_Click() mergeCellData End Sub Sub mergeCellData() On Error GoTo ErrorHandler Dim cell As Range Dim mySelRange As Range Set mySelRange = Selection Dim mergeText As String ' LOOP THROUGH SELECTION OBJECT AND ' EXTRACT ALL CELL VALUES AND STORE IT IN A VARIABLE. For Each cell In mySelRange mergeText = mergeText & cell.Value & " " Next cell ' NOW, MERGE AND COMBINE THE EXTRACTED TEXTS (NUMBERS AS WELL) AND ' ADD IT TO THE FIRST CELL. With mySelRange .Clear .Cells(1).Value = mergeText .HorizontalAlignment = xlGeneral .VerticalAlignment = xlCenter .Merge .WrapText = True End With ErrorHandler: ' End Sub
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.
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.
Therefore, get rid of the number (or convert the number to text by adding a single quoteto merge the cells into one. I personally prefer the first example (using VBA code).