How to Combine Multiple Columns into One Column in Excel

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel, there are several suitable methods to combine multiple columns into one column. In this article, you’ll learn how you can apply different approaches to merge data from multiple columns into a single column with examples and proper illustrations.


1. Using 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 a meaningful address in Column E under the Combined Text header.

Use of CONCATENATE or CONCAT Function to Join Multiple Columns in Excel

We can use the CONCATENATE or CONCAT function to serve the purpose. In the first output Cell E5, the required formula will be:

=CONCATENATE(B5,C5,D5)

Or,

=CONCAT(B5,C5,D5)

Use of CONCATENATE or CONCAT Function to Join Multiple Columns in Excel

After pressing Enter and using Fill Handle to autofill the rest of the cells in Column E, we’ll get the combined single column as shown in the picture below.

Use of CONCATENATE or CONCAT Function to Join Multiple Columns in Excel


2. Inserting Ampersand (&) to Combine Multiple Columns into Single Column in Excel

We can also use Ampersand (&) to concatenate or join texts more easily. Assuming that we don’t have any delimiter with the texts in the cells but while joining texts from a row, we’ll have to insert a delimiter.

Use of Ampersand (&) to Combine Multiple Columns into Single Column

In the output Cell E5, the required formula with the uses of Ampersand (&) will be:

=B5&", "&C5&", "&D5

Use of Ampersand (&) to Combine Multiple Columns into Single Column

Press Enter, autofill the entire Column E and you’ll get all the combined texts into a single column right away.

Use of Ampersand (&) to Combine Multiple Columns into Single Column


3. Inserting 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:

=TEXTJOIN(", ",TRUE,B5,C5,D5)

Insert TEXTJOIN Function to Combine Multiple Columns into Column in Excel

After pressing Enter and dragging down to the last cell in Column E, you’ll get the concatenated texts in a single column at once.

Insert TEXTJOIN Function to Combine Multiple Columns into Column in Excel


4. Stacking Multiple Columns into One Column in Excel

Now our dataset has 4 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. In a word, we’ll stack all the data in a single column.

Stack Multiple Columns into One Column in Excel

📌 Step 1:

➤ Select the range of cells (B4:E6) containing the primary data.

➤ Name it with a text in the Name Box.

Stack Multiple Columns into One Column in Excel

📌 Step 2:

➤ In the output Cell G5, type the following formula:

=INDEX(Data,1+INT((ROW(A1)-1)/COLUMNS(Data)),MOD(ROW(A1)-1+COLUMNS(Data),COLUMNS(Data))+1)

Stack Multiple Columns into One Column in Excel

📌 Step 3:

➤ Press Enter and you’ll get the first value from the 4th row in Cell G5.

➤ Now use Fill Handle to drag down along the column until you find a #REF error.

And finally, you’ll be displayed the following output.

Stack Multiple Columns into One Column in Excel

🔎 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.

5. Using Notepad to Merge Columns Data in Excel

We can also use a Notepad to combine multiple columns into one column. Let’s go through the following steps:

📌 Step 1:

➤ Select the range of cells (B5:D9) containing the primary data.

➤ Press CTRL+C to copy the selected range of cells.

Use of Notepad to Merge Columns Data in Excel

📌 Step 2:

➤ Open a notepad file.

➤ Paste CTRL+V to paste the selected data here.

Use of Notepad to Merge Columns Data in Excel

📌 Step 3:

➤ Press CTRL+H to open the Replace dialogue box.

➤ Select a tab between two texts aside in your notepad file and copy it.

➤ Paste it into the Find what box.

Use of Notepad to Merge Columns Data in Excel

📌 Step 4:

➤ Type “, “ in the Replace with box.

➤ Press the option Replace All and you’re done.

Use of Notepad to Merge Columns Data in Excel

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

Use of Notepad to Merge Columns Data in Excel

📌 Step 5:

➤ Now copy the entire text from the notepad.

Use of Notepad to Merge Columns Data in Excel

📌 Step 6:

➤ And finally, paste it into the output Cell E5 in your Excel spreadsheet.

The resultant data in Column E will now be as follows:

Use of Notepad to Merge Columns Data in Excel


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

We can also use the VBA method to stack multiple columns into a single column. In the following picture, Column G will show the stacked data.

Use VBA Script to Join Columns into One Column in Excel

📌 Step 1:

➤ Right-click on the Sheet name in your workbook and press View Code.

A new module window will appear where you’ll have to simply paste the following codes:

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

📌 Step 2:

➤ After pasting the codes, press F5 to run the code.

➤ Assign a macro name in the Macro dialogue box.

➤ Press Run.

Use VBA Script to Join Columns into One Column in Excel

📌 Step 3:

➤ Select the primary range of data (B4:E6) in the Select Range box.

➤ Press OK.

Use VBA Script to Join Columns into One Column in Excel

📌 Step 4:

➤ Select the output Cell G5 after enabling the Destination Column box.

➤ Press OK and you’re done.

Use VBA Script to Join Columns into One Column in Excel

Like in the following picture, you’ll be shown the combined and stacked data in the output column.

Use VBA Script to Join Columns into One Column in Excel


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.


Concluding Words

I hope all of these simple methods mentioned above will now help you to apply them in your Excel spreadsheets when necessary. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.


<< Go Back to Range | Concatenate | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

6 Comments
  1. Legend! Section 4 is amazing. I’ve been looking for months for this. I was using stackarray() until now but sometimes it failed. Thanks, thanks and thanks!

  2. Amazing! Need little more help.
    Using your formula, the result displays as
    A1,B1,C1,C4,A2,B2,C2,C4….

    I need the result to be displayed as per column, then next full column:
    A1,A2,A3,A4,A5,B1,B2,B3,B4,B5,C1,C2,C3 and so on…

    • Hello, ADIL M!
      Thank you for your appreciation and query.
      Regarding your query, I have thought of two cases.
      Case 1: Stacking all data in a single column but different rows with your desired order (A1, A2, B1, B2, and so on).
      Case 2: Combining data of columns into separate cells, i.e. A1:A4 range in cell D1, B1:B4 range in cell D2, C1:C4 range in cell D3, and so on.

      Download Link:
      The file is attached here for you.
      Solution.xlsx

      ⊕ 1st Case: Stacking All Data in a Single Column
      1. Use the following formula in cell G5.
      =INDEX(Data,(MOD(ROW(A1)-1,3)+1),ROUNDUP(ROW(A1)/3,0))
      Formula with INDEX Function
      2. Following, drag the fill handle downward to accomplish your full result.
      Full result with INDEX Function
      Now, this formula may look like a complex one. So, for your better understanding, I am breaking down the formula below.

      Formula Breakdown:
      >> MOD(ROW(A1)-1,3)

      This will give you the argument of the row number in the INDEX function. If you drag the fill handle it would give you the next row numbers as per your data range.
      Result: 1,2,3,1,2,3,1,2,3,1,2,3 (As our range has 3 rows and 4 columns)

      >> ROUNDUP(ROW(A1)/3,0)

      This part will give you the sequence of column numbers for your data range. If you drag the fill handle below you would get all the column numbers individually for all the values of your range.
      Result: 1,1,1,2,2,2,3,3,3,4,4,4

      >> INDEX(Data,(MOD(ROW(A1)-1,3)+1),ROUNDUP(ROW(A1)/3,0))

      This will take your lookup array and generate the values individually from the lookup as per the row numbers and column numbers that you have got in the upper processes. So after dragging the fill handle below, you will get the whole data range in a single column in your desired format.
      Result: 1, Andy, 6-Jan, 2, Miller, 8-Apr, 3, Keith, 12-Jun, 4, Robert, 19-Aug

      ⊕ 2nd Case: Combining Data of Columns into Separate Cells
      You can also use the TEXTJOIN function in this regard.
      1. Insert the following formula in cell E5 now.
      =TEXTJOIN(" ",TRUE,INDEX($B$5:$E$9,ROW($A$1),ROW(A1)):INDEX($B$5:$E$9,ROW($A$5),ROW(A1)))
      TEXTJOIN Function to Get Desired Result
      Note that the TEXTJOIN function can join an array of text. We have used the INDEX function as cell reference in the formula, to specify the array.
      2. Following, drag the fill handle below to get all results.
      All Results with TEXTJOIN Function

      Formula Breakdown:
      >> INDEX($B$5:$E$9,ROW($A$1),ROW(A1))

      This will return you the first row and first column data from your array. Here, you must reference your row_num argument with absolute reference.
      Result: 26. 26 is situated in cell B5.

      >> INDEX($B$5:$E$9,ROW($A$5),ROW(A1))

      This will return you the last row of the following column data of your array.
      Result: 18. 18 is situated in cell B9.

      >> INDEX($B$5:$E$9,ROW($A$1),ROW(A1)):INDEX($B$5:$E$9,ROW($A$5),ROW(A1))
      This refers to array B5:B9. To get this clearly, evaluate the formula from the Formulas tab.

      >> TEXTJOIN(” “,TRUE,INDEX($B$5:$E$9,ROW($A$1),ROW(A1)):INDEX($B$5:$E$9,ROW($A$5),ROW(A1)))

      This will join the texts from the first row to the last row of the first column of data with a space between each row of data.
      Result: 26 44 15 25 18

      Regards,
      Tanjim Reza

  3. #4 was very helpful for me. Thank you so much!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo