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 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:
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.
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.
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. 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:
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.
Read More: Combine Text in Excel (8 Suitable Ways)
- Concatenate Date/Day, Month, and Year in Excel
- How to Concatenate Email Addresses in Excel (4 Effective Ways)
- Excel INDEX MATCH to Concatenate Multiple Results (With 2 Easy Steps)
- Carriage Return in Excel Formula to Concatenate (6 Examples)
- How to Concatenate and Keep Currency Format in Excel (3 Methods)
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.
📌 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:
📌 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. 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.
📌 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:
Read More: How to Merge Rows in Excel (5 Easy Ways)
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.
📌 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.
Download Practice Workbook
You can download the Excel workbook that we’ve used to prepare this article.
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.
- How to Add a 1 in Front of Numbers in Excel (7 Easy Ways)
- Opposite of Concatenate in Excel (4 Options)
- How to Concatenate Range in Excel (5 Useful Methods)
- Concatenate Date and Time in Excel (4 Formulas)
- How to Concatenate with Space in Excel (3 Suitable Ways)
- Concatenate Numbers with Leading Zeros in Excel (6 Methods)
- Excel CONCATENATE Showing Formula Not Result (5 Solutions)