In case you have a requirement on combining multiple columns to on column & you did not have a clue then this whole article is for you. In this article we are going to learn how to combine multiple columns to one column using vba code.
From below snapshot:-
Following is the snapshot of require output:-
We need to follow the below steps:
Using a Formula CONCATENATE + TRANSPOSE to Combine Values. The best way to combine text. Below are the steps to merge the first and the last name using ampersand: Click on the first cell of the column where you want the combined names to appear (C2). Type equal sign (=). Select the cell containing the first name (A2) followed by an ampersand (&).
Enter the following code in the standard module
Const shName1 As String = 'Sheet1' 'Change sheet name here
Const shName2 As String = 'Sheet2'
Dim arr, arrNames
arrNames = .Range('F1', .Cells(1, Columns.Count).End(xlToLeft))
For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
arr = .Cells(i, 1).Resize(, 4)
With .Cells(Rows.Count, 1).End(xlUp)
.Offset(1).Resize(UBound(arrNames, 2), 4) = arr
.Offset(1, 5).Resize(UBound(arrNames, 2)) = Application.Transpose(arrNames)
As you execute the macro; the macro will transfer the data from multiple columns to a single column.
In this way we can combine multiple columns data in a single column.
This tutorial will talk about how to combine multiple cell contents into one single cell as below screenshot shown. In Excel, you can use the & symbol, CONCATENATE or TEXTJOIN function to solve this task quickly and easily.
By using the & symbol, you can join multiple cells into one cell, please apply the below formula into a blank cell:
Then, press Enter key, all of the specified cell contents have been merged into one cell, see screenshot:
Note: In the above formula: A2, A3, A4, A5, A6, A7 and A8 are the cells that you want to combine; “ ” is a space delimiter that separate the combined data, if you want to use other delimiters, you just need to replace the space string with other separator, such as “,”, “-” etc.
If there is long list of cells need to be merged, the above formula will be too long to type, here, I will introduce a simple formula which is created by CONCATENATE and TRANSPOSE functions.
1. Please apply the following formula into a blank cell where you want to put the result:
2. Then select the entire formula which inside the CONCATENATE function, and then press F9 key on the keyboard, this formula will be converted to an array, see screenshots:
3. And then, you should remove the curly brackets from the start and the end of the array, see screenshots:
4. After removing the curly brackets, please press Enter key directly, and all the cell contents have been merged into one cell, see screenshot:
You can also view the below demo to see the entire operation process:
1. In the above formula, A2:A8 is the range of cells that you want to combine; “,” is a comma delimiter that separate the combined data, please change them to your need.
2. After finishing this formula, a comma will be displayed at the end of the last cell value, to remove this comma, you just need to double click the formula cell, and delete the comma after the last value.
In Excel 2019 or Office 365, there is new TEXTJOIN function may help you to combine cell values from different cells into a single cell with ease.
The generic syntax of the TEXTJOIN is:
Please enter or copy the following formula into a blank cell:
And then, press Enter key to get the result, see screenshot:
Note: In the above formula, A2:A8 is the range of cells that you want to combine; “-” is a hyphen delimiter that separate the combined data, please change them to your need.
Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.