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.

**Table of Contents**hide

**Download Practice Workbook**

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

**6 Approaches to Combine Multiple Columns into One Column in Excel**

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

In the following picture, the three columns are representing 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.

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)`

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.

**Related Content:** Combine Text from Two or More Cells into One Cell in Excel (5 Methods)

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

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.

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

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

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

**3. Insert TEXTJOIN Function to Combine Multiple Columns into 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)`

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.

**4. Stack 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.

**📌**** Step 1:**

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

➤ Name it with a text in the **Name Box**.

**📌**** 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)`

**📌**** 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.

**🔎**** 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. Use of 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.

**📌**** Step 2:**

➤ Open a notepad file.

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

**📌**** 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.

**📌**** Step 4:**

➤ Type** “, “** in the **Replace with** box.

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

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

**📌**** Step 5:**

➤ Now copy the entire text from the notepad.

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

**6. Use 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.

**📌**** 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**.

**📌**** Step 3:**

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

➤ Press **OK**.

**📌**** Step 4:**

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

➤ Press **OK **and you’re done.

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

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

## Further Readings

- How to Concatenate Two Columns In Excel (5 Methods)
- Combine Text in Excel (8 Suitable Ways)
- Concatenate Numbers in Excel (4 Quick Formulas)
- Combine Multiple Cells Into One Cell Separated By Comma In Excel
- How to Combine Date and Text in Excel (5 Ways)
- Concatenate Date and Time in Excel (4 Formulas)
- Opposite of Concatenate in Excel (4 Options)

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!

Hello, JUAN! Glad to know you’ve found the solutions finally from our article!