Combine Two Columns Into One In Excel

Excel
  1. How To Combine Two Columns Into One In Excel
  2. How To Combine Two Cells In Excel
  3. Convert 2 Columns To 1 In Excel

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:-

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 Worksheets(shName2) With.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(arrNames, 2), 4) = arr.

  • If you want to join the text from multiple cells into one cell, you also can use the CONCATENATE function instead of Ampersand operator. Just following the below steps to join combine the text from B1:B3 into one Cell C1. 1# Select the cell in which you want to put the combined text. (Select Cell C1) 2# type the following formula in Cell C1.
  • For instance, you want to combine the cells of each row across column B, column C, column D and column E into one cell with commas, please use below formula in a blank cell. =B3&','&C3&','&D3&','&E3 Press Enter key, then the cells are combined with comma separated.

How To Combine Two Columns Into One In Excel

Following is the snapshot of require output:-

We need to follow the below steps:

  • Click on Developer tab
  • From Code group select Visual Basic
Combine

Enter the following code in the standard module

Sub MultipleColumns2SingleColumn()

Const shName1 As String = 'Sheet1' 'Change sheet name here

Const shName2 As String = 'Sheet2'

In excel can you combine two columns into one

Dim arr, arrNames

With Worksheets(shName1)

arrNames = .Range('F1', .Cells(1, Columns.Count).End(xlToLeft))

For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row

One

arr = .Cells(i, 1).Resize(, 4)

With Worksheets(shName2)

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)

End With

End With

How To Combine Two Cells In Excel

Next

End With

End Sub

As you execute the macro; the macro will transfer the data from multiple columns to a single column.

Convert 2 Columns To 1 In Excel

In this way we can combine multiple columns data in a single column.