Concatenating multiple columns in Excel manually can be quite a tedious task, especially when dealing with large datasets. But don’t worry! There’s a better way to do it.
Did You Know? According to Microsoft, over 750 million people use Excel worldwide, and businesses waste 30% of their time on manual Excel tasks. Why spend hours merging columns manually when a VBA macro can do it in seconds?
Rather than relying on manual work or formulas, you can easily automate this process using a VBA macro. With just a few clicks, you can quickly combine multiple columns into one without losing any data.
In this guide, I will walk you through step by step on how to combine multiple columns into a single column using a macro in Excel. Not only that, but you will also learn how to merge more than two columns while using separators like spaces, commas, and semicolons. On top of that, I’ll show you how to remove duplicates effortlessly while merging cells in Excel.
Why Use VBA Instead of Excel Formulas?
| Method | Best For | Automation | Limitations |
| CONCATENATE / & | Small datasets | No | Cannot handle blank cells well |
| TEXTJOIN (Excel 2016+) | Dynamic merging | No | Requires delimiter |
| VBA Macro | Large datasets | Yes | Requires enabling macros |
Pro Tip: If you need a one-time combination, use TEXTJOIN. If you need to merge columns frequently, use VBA.
For merging only two columns efficiently, check out How to Combine Two Columns in Excel.
Step-by-Step Guide: Combine Multiple Columns Using VBA
Step 1: Open the VBA Editor
- You need to press Alt + F11 to open the VBA Editor.
- Then click on Insert > Module.
Step 2: Paste This VBA Macro to Combine Columns
Sub CombineColumns()
Dim ws As Worksheet
Dim lastRow As Long, lastCol As Long
Dim i As Long, j As Long
Dim combinedText As String
' Set active worksheet
Set ws = ActiveSheet
' Find the last used row and column dynamically
lastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lastCol = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
' Loop through each row to combine columns
For i = 1 To lastRow
combinedText = "" ' Reset string for each row
For j = 1 To lastCol
' Add cell value if it's not empty
If ws.Cells(i, j).Value <> "" Then
combinedText = combinedText & ws.Cells(i, j).Value & " "
End If
Next j
' Trim and store in next column
ws.Cells(i, lastCol + 1).Value = Trim(combinedText)
Next i
MsgBox "Columns combined successfully!", vbInformation, "Done"
End SubStep 3: Run the Macro
You can place your data anywhere on the sheet.
Now, you can run the macro (Shortcut: Alt + F8) or Developers > Macros > Run.
You will see combined values in the next available column.
Example Input:
| Column A | Column B | Column C |
| John | Doe | 12345 |
| Alice | Smith | 67890 |
Output (Next Available Column):
| Column A | Column B | Column C | Column D |
| John | Doe | 12345 | John Doe 12345 |
| Alice | Smith | 67890 | Alice Smith 67890 |
Output result in an image:
Customize the Macro
1. Add a Custom Separator (Comma, Space, Semicolon, etc.)
To add a comma, Space, or Semicolon Separator, replace” ” in this line:
combinedText = combinedText & ws.Cells(i, j).Value & " "With commas (,), semicolons (😉, or any separator you need.
Example: To add a comma(,) separator, replace it with the following line:
combinedText = combinedText & ws.Cells(i, j).Value & ", "and for adding a semicolon(;)
combinedText = combinedText & ws.Cells(i, j).Value & ";"In Excel, the opposite of concatenate is to break the combined text into separate columns. See How to Split Text in Excel.
2. Remove Duplicates While Merging Columns
If you have duplicate values like:
| Apple | Banana | Apple |
| 123 | 123 | 456 |
| John | Doe | John |
and want to remove duplicates while merging, you can write a macro like below:
Sub CombineColumns()
Dim ws As Worksheet
Dim lastRow As Long, lastCol As Long
Dim i As Long, j As Long
Dim combinedText As String
Dim dict As Object
' Set active worksheet
Set ws = ActiveSheet
' Find the last used row and column dynamically
lastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lastCol = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
' Loop through each row to combine columns
For i = 1 To lastRow
combinedText = "" ' Reset string for each row
Set dict = CreateObject("Scripting.Dictionary") ' Initialize dictionary for unique values
For j = 1 To lastCol
' Add cell value if it's not empty and not already in dictionary
If ws.Cells(i, j).Value <> "" And Not dict.exists(ws.Cells(i, j).Value) Then
dict.Add ws.Cells(i, j).Value, Nothing
combinedText = combinedText & ws.Cells(i, j).Value & " "
End If
Next j
' Trim and store in next column
ws.Cells(i, lastCol + 1).Value = Trim(combinedText)
Next i
MsgBox "Columns combined successfully!", vbInformation, "Done"
End Sub
When you run this macro, it will remove duplicate values like:
| Apple | Banana | Apple | Apple Banana |
| 123 | 123 | 456 | 123 456 |
| John | Doe | John | John Doe |
3. Merge Across Multiple Sheets
Modify this line:
Set ws = ActiveSheetTo loop through all sheets:
For Each ws In ThisWorkbook.Sheets4. Handle Large Datasets Efficiently
- Use VBA Arrays instead of looping cell by cell for faster execution.
- Optimize memory usage by setting Application.ScreenUpdating = False at the start and True at the end.
5. Combine Only Specific Columns
If you want to combine only specific columns instead of all columns in the sheet, you can do it by defining which columns to include in the macro.
Here is an example macro to combine only A, C, and E Columns.
Sub CombineSpecificColumns()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long, j As Long
Dim combinedText As String
Dim dict As Object
Dim columnsToCombine As Variant
' Set active worksheet
Set ws = ActiveSheet
' Define the columns to combine (modify this as needed)
columnsToCombine = Array(1, 3, 5) ' Example: Column A (1), Column C (3), Column E (5)
' Find the last used row dynamically
lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
' Loop through each row to combine only selected columns
For i = 1 To lastRow
combinedText = "" ' Reset string for each row
Set dict = CreateObject("Scripting.Dictionary") ' Initialize dictionary for unique values
For j = LBound(columnsToCombine) To UBound(columnsToCombine)
' Get column index from the array
Dim colIndex As Integer
colIndex = columnsToCombine(j)
' Add cell value if it's not empty and not already in dictionary
If ws.Cells(i, colIndex).Value <> "" And Not dict.exists(ws.Cells(i, colIndex).Value) Then
dict.Add ws.Cells(i, colIndex).Value, Nothing
combinedText = combinedText & ws.Cells(i, colIndex).Value & " "
End If
Next j
' Trim and store result in **Column F (6th column)**
ws.Cells(i, 6).Value = Trim(combinedText) ' Column F is the 6th column
Next i
MsgBox "Selected columns combined successfully!", vbInformation, "Done"
End SubHow This Modification Works
- The columnsToCombine array allows you to specify which columns to merge (e.g., {1, 3, 5} for Columns A, C, and E).
- The macro only loops through these columns instead of merging all columns.
- The merged result is stored in the next available column without duplicates.
Example:
Input Data
| Column A | Column B | Column C | Column D | Column E |
| John | 12345 | Doe | NYC | USA |
| Alice | 56789 | Smith | LA | USA |
If we merge only Columns A, C, and E, the output will be:
| Merged Column |
| John Doe USA |
| Alice Smith USA |
Tips:
- Change the columns: Modify columnsToCombine = Array(1, 3, 5) to include different column numbers.
- Add a custom separator: Replace ” ” with “, ” or “; ” to separate values differently.
- Store results in a specific column: Change ws.Cells(i, UBound(columnsToCombine) + 2).Value to a fixed column like ws.Cells(i, 10).Value.
6. Exclude Specific Words or Values While Merging
If you want to exclude specific words or values while merging, you need to add a list of excluded words and check each value before adding it to the merged result.
VBA Macro to Exclude Specific Words
Sub CombineAllColumnsExcludeWords()
Dim ws As Worksheet
Dim lastRow As Long, lastCol As Long
Dim i As Long, j As Long
Dim combinedText As String
Dim dict As Object
Dim excludedWords As Variant
Dim cellValue As String
Dim word As Variant
Dim skipValue As Boolean
' Set active worksheet
Set ws = ActiveSheet
' Define words or values to exclude
excludedWords = Array("Remove", "Smith") ' Modify as needed
' Find the last used row and last used column dynamically
lastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lastCol = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
' Loop through each row to combine all columns
For i = 1 To lastRow
combinedText = "" ' Reset string for each row
Set dict = CreateObject("Scripting.Dictionary") ' Dictionary for unique values
For j = 1 To lastCol
' Get cell value
cellValue = Trim(ws.Cells(i, j).Value)
' Check if the value is in the exclusion list
skipValue = False
For Each word In excludedWords
If StrComp(cellValue, word, vbTextCompare) = 0 Then
skipValue = True
Exit For
End If
Next word
' Add cell value if it's not empty, not already in dictionary, and not excluded
If cellValue <> "" And Not dict.exists(cellValue) And Not skipValue Then
dict.Add cellValue, Nothing
combinedText = combinedText & cellValue & " "
End If
Next j
' Trim and store result in **Column F (6th column)**
ws.Cells(i, 6).Value = Trim(combinedText)
Next i
MsgBox "All columns combined successfully in Column F (excluding specified words)!", vbInformation, "Done"
End SubHow This Works:
- Exclusion List: Modify excludedWords = Array(“N/A”, “Unknown”, “Remove”, “NULL”) to add more words.
- Case-Insensitive Matching: The script checks words without case sensitivity (vbTextCompare).
- Avoids Duplicates & Excluded Words: Ensures no duplicate values are merged and excluded words are ignored.
Example Usage
Input Data
| Column A | Column B | Column C | Column D |
| John | Remove | Doe | NYC |
| Alice | Smith | 123 | LA |
Output in Column F (Excluding “Remove” and “Smith”)
| Merged Column (F) |
| John Doe NYC |
| Alice 123 LA |
Pro Tips for Combining Columns Efficiently
- Backup Your Data: Always save your work before running VBA macros.
- Run on Multiple Sheets: Modify Set ws = ActiveSheet to loop through all sheets.
- Handle Dates Properly: Format cells as Text before combining to avoid losing date formats.
- Skip Empty Cells: The macro already ignores blank cells, ensuring clean data output.
Alternative Methods: VBA vs Formulas vs Power Query
| Method | Best For | Pros | Cons |
| TEXTJOIN Formula | Simple merging | No VBA needed, dynamic updates | Limited in older Excel versions |
| Power Query | Large datasets | Works across multiple sheets | Requires setup |
| VBA Macro | Automation | Fast, flexible, removes duplicates | Requires enabling macros |
Download the Excel Practice Sheet (Hands-on Learning!)
Want to practice merging columns in Excel with real-world data? Download this interactive practice sheet and try it yourself!
Download Combine Multiple Column Excel Practice Sheet
Combine Multiple Columns FAQs
1. Can I replace text in password-protected files?
No, unless you modify the macro to unlock the workbook before replacing text.
2. How can I replace text only in a specific column?
Modify the VBA code like this:
ws.Columns(“B:B”).Replace What:=”TechCorp”, Replacement:=”NextGen Supplies”, LookAt:=xlPart
3. Can this macro work for CSV files?
No, but you can first convert CSV files to Excel, run the macro, and save them back as CSV.
4. Is there a way to remove duplicates while merging?
Yes, you can remove duplicates automatically by using a dictionary-based approach.
Want help combining multiple columns with a VBA macro? Or looking for more advanced Excel tricks? Drop a comment below! I’d love to help.