How to Combine Multiple Columns into One Column in Excel

Method 1 – Using the CONCATENATE or CONCAT Function to Join Multiple Columns into One Column in Excel

In the following picture, the three columns represent some random addresses with split parts. We have to merge each row to make an address in Column E under the Combined Text header.

• In the first output Cell E5, the required formula will be:

OR:

• Press Enter and drag the Fill Handle from the cell E5 down to fill the other cells.

Method 2 – Inserting an Ampersand (&) to Combine Multiple Columns into a Single Column in Excel

The individual cells don’t have a delimiter, so we’ll include one.

• In the output Cell E5, the required formula is:

• Press Enter and autofill the entire Column E.

Method 3 – Inserting the TEXTJOIN Function to Combine Multiple Columns into One Column in Excel

If you’re using Excel 2019 or Excel 365 then the TEXTJOIN function is another great option to meet your purposes.

• The required formula to join multiple texts with the TEXTJOIN function in Cell E5 will be:

• Press Enter and drag down to the last cell in Column E.

Method 4 – Stacking Multiple Columns into One Column in Excel

Our dataset has four random columns ranging from Column B to Column E. Under the Combine Column header, we’ll stack the values from the 4th, 5th, and 6th rows sequentially.

Steps:

• Select the range of cells (B4:E6) containing the primary data.
• Name it as Data in the Name Box (to the left of the formula bar).

• In the output Cell G5, use the following formula:

• Press Enter and you’ll get the first value from the 4th row in Cell G5.
• Use the Fill Handle to drag down along the column until you find a #REF error.

How Does the Formula Work?

• COLUMNS(Data): The COLUMNS function inside the MOD function here returns the total number of columns available in the named range (Data).
• ROW(A1)-1+COLUMNS(Data): The combination of ROW and COLUMNS functions here defines the dividend of the MOD function.
• MOD(ROW(A1)-1+COLUMNS(Data), COLUMNS(Data))+1: This part defines the column number of the INDEX function and for the output, the function returns ‘1’.
• 1+INT((ROW(A1)-1)/COLUMNS(Data)): The row number of the INDEX function is specified by this part where the INT function rounds up the resultant value to the integer form.

Method 5 – Using Notepad to Merge Column Data in Excel

Step:

• Select the range of cells (B5:D9) containing the primary data.
• Press Ctrl + C to copy the selected range of cells.

• Open Notepad.
• Press Ctrl + V to paste the data.

• Press Ctrl + H to open the Replace dialog box.
• Select a tab between two values in your notepad file and copy it.
• Paste it into the Find what box.

• Type “, “ (comma and space) in the Replace with box.
• Press the option Replace All.

• All the data in your Notepad file will look like in the following picture.

• Copy the entire text from Notepad.

• Paste it into the output Cell E5 in your Excel spreadsheet.

Method 6 – Using VBA Script to Join Columns into One Column in Excel

In the following picture, Column G will show the stacked data.

Steps:

• Right-click on the Sheet name in your workbook and press View Code.
• A new module window will appear. Paste the following code into it:

Option Explicit
Sub StackColumns()
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng As Range
Dim RowIndex As Integer
Set Rng1 = Application.Selection
Set Rng1 = Application.InputBox("Select Range:", "Stack Data into One Column", Rng1.Address, Type:=8)
Set Rng2 = Application.InputBox("Destination Column:", "Stack Data into One Column", Type:=8)
RowIndex = 0
Application.ScreenUpdating = False
For Each Rng In Rng1.Rows
    Rng.Copy
    Rng2.Offset(RowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    RowIndex = RowIndex + Rng.Columns.Count
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

• Press F5 to run the code.
• Choose the macro name in the Macro dialog box.
• Press Run.

• Select the primary range of data (B4:E6) in the Select Range box.
• Press OK.

• Select the output Cell G5 after in the Destination Column box.
• Press OK.

• Here’s the result.

Download the Practice Workbook

<< Go Back to Range | Concatenate | Learn Excel