In this article, we’ll show you how you can concatenate a range of cells with a given separator using VBA in Excel. You’ll learn to concatenate the rows within a range, the columns within a range, and finally, both the rows and columns within a range.
Download Practice Workbook
You can download the workbook to practice yourself.
4 Easy Ways to Concatenate Range with Separator Using VBA in Excel
Here we’ve got a data set with the names of some products and their sales of 2 years of a company called Mars Group.
Today our objective is to concatenate values from this range with a separator using Visual Basic Application (VBA) in Excel.
1. Concatenate Rows with Separator in Excel
First of all, we’ll try to concatenate a range that consists of only a row with VBA. Let’s try to concatenate the sales of each product of these two years in single cells.
Steps:
- In the beginning, go to the Developer tab >> click on Visual Basic.
- Now, Microsoft Visual Basic for Application box will open.
- After that, click on Insert >> select Module.
- Then, write the following code in your Module.
Sub Concatenate_Rows_with_Separator()
Separator = InputBox("Enter the Separator: ")
Output = ""
For row_no = 1 To Selection.Rows.Count
For col_no = 1 To Selection.Columns.Count
If VarType(Selection.Cells(row_no, col_no)) = 8 Then
Output = Output + Selection.Cells(row_no, col_no) + Separator
Else
Output = Output + Str(Selection.Cells(row_no, col_no)) + Separator
End If
Next col_no
Selection.Cells(row_no, Selection.Columns.Count + 1) = Mid(Output, 1, Len(Output) - 1)
Output = ""
Next row_no
End Sub
Code Breakdown
- Firstly, we created a Sub Procedure as Concatenate_Rows_with_Separator.
- Then, we inserted an InputBox to enter a separator.
- Next, we used For Next loop and IF Statement to concatenate rows.
- Next, click on the Save button and go back to your worksheet.
- After that, select cell range C5:D11.
- Further, go to the Developer tab >> click on Macros.
- Now, the Macros dialog box will appear.
- Then, select Concatenate_Rows_with_Seperator.
- Lastly, click on Run.
- Next, You’ll get an Input box asking you to enter the separator.
- Here, we’ve entered a comma (,) as the separator. You enter anything you like.
- After that, press OK.
- Finally, you’ll get the sales record of each product concatenated in a column right to the selected range.
Read More: How to Concatenate String and Integer using VBA
2. Join Columns with Separator in Excel
Next, we’ll try to concatenate the range according to columns with VBA. Let’s try to concatenate the sales of each year of all the products in single cells.
Steps:
- Firstly, insert a new module to develop a Macro following the steps shown in Method 1.
- Then, insert the following VBA Code in the module.
Sub Join_Columns()
Separator = InputBox("Enter the Separator: ")
Output = ""
For row_no = 1 To Selection.Columns.Count
For col_no = 1 To Selection.Rows.Count
If VarType(Selection.Cells(col_no, row_no)) = 8 Then
Output = Output + Selection.Cells(col_no, row_no) + Separator
Else
Output = Output + Str(Selection.Cells(col_no, row_no)) + Separator
End If
Next col_no
Selection.Cells(row_no, Selection.Columns.Count + 1) = Mid(Output, 1, Len(Output) - 1)
Output = ""
Next row_no
End Sub
Code Breakdown
- Firstly, we created a Sub Procedure as Join_Columns.
- Then, we inserted an InputBox to enter a separator.
- Next, we used For Next loop and IF Statement to concatenate columns.
- Next, select cell range C5:D11.
- After that, go to the Developer tab >> click on Macros.
- Then, select the macro named Join_Columns.
- Now, click on Run.
- Afterward, you’ll get an Input Box asking you to enter the separator.
- This time we’ve inserted a Hyphen (-) as the separator. However, you can use anything you like.
- Lastly, click on OK.
- Thus, you’ll find the sales of all the products in each year concatenated in a column right to your data set.
Similar Readings
- How to Concatenate Two Columns In Excel (5 Methods)
- Concatenate in Excel (3 Suitable Ways)
- Concatenate Not Working in Excel (3 Reasons with Solutions)
- How to Concatenate Apostrophe in Excel (6 Easy Ways)
- Combine Text in Excel (8 Suitable Ways)
3. Concatenate Range Considering Both Rows and Columns with Separator in Excel
In the third method, we’ll develop a Macro to concatenate all the cells of a range within a single cell with VBA.
Steps:
- To start with, insert a new module following the steps shown in Method 1.
- Next, use the following VBA code for this purpose.
Sub Concatenate_Rows_and_Columns()
Separator = InputBox("Enter a Separator: ")
Location = InputBox("Enter Cell Reference where You Want to Concatenate the Range: ")
Output = ""
For row_no = 1 To Selection.Rows.Count
For col_no = 1 To Selection.Columns.Count
If VarType(Selection.Cells(col_no, row_no)) = 8 Then
Output = Output + Selection.Cells(row_no, col_no) + Separator
Else
Output = Output + Str(Selection.Cells(row_no, col_no)) + Separator
End If
Next col_no
Next row_no
Range(Location) = Mid(Output, 1, Len(Output) - 1)
End Sub
Code Breakdown
- This code creates a Macro called Concatenate_Rows_with_Separator.
- It concatenates all the values from a selected range of cells into a single cell.
- It takes two inputs given by the user. One is the separator by which the values will be separated, and the other is the cell location where the values will be concatenated.
- The code uses the Addition sign (+) of VBA to concatenate two strings.
- In case, a value within the range is not a string, the code first converts it into a string and then concatenates it.
- Then, select cell range C5:D7.
- After that, go to the Developer tab >> click on Macros.
- Now, select Concatenate_Rows_and_Columns macro.
- Next, click on Run.
- Again, you’ll get the same Input box asking you to enter the separator.
- Here, we’ve again entered a comma (,).
- Then, click on OK.
- This time, you’ll get another Input Box asking you to enter the reference of the cell where you want to concatenate the range.
- Here, we’ve inserted C13 in it.
- Lastly, click on OK.
- Finally, you’ll get the selected range of cells merged into a single cell in your desired cell location (Cell C13 in this example).
Read More: How to Combine Rows into One Cell in Excel
4. Employ User-Defined Function to Concatenate Range with Separator
In the last method, we will create a user-defined function to concatenate range with a separator in Excel.
Here are the steps to do that.
Steps:
- In the beginning, insert a new module to develop a Macro following the steps shown in Method 1.
- Then, insert the following VBA Code in the module.
Function ConcatenateSales(ByVal cell_range1 As Range, _
Optional ByVal seperator1 As String) As String
Dim String1 As String
Dim Array1 As Variant
Dim row As Long, colm As Long
Array1 = cell_range1.Value
For row = 1 To UBound(Array1, 1)
For colm = 1 To UBound(Array1, 2)
If Len(Array1(row, colm)) <> 0 Then
String1 = String1 & (seperator1 & Array1(row, colm))
End If
Next
Next
If Len(String1) <> 0 Then
String1 = Right$(String1, (Len(String1) - Len(seperator1)))
End If
ConcatenateSales = String1
End Function
Code Breakdown
- To start with, we created a user defined function as ConcatenateSales with required argument cell_range1 as Range and optional argument separator1 as String.
- Then, we declared String1 as String, Array1 as Variant, row and colm as Long.
- Next, we set Array1 as the value of cell_range.
- Finally, we used For loop, IF conditions and UBound, Right, Len etc functions to create the ConcatenateSales function.
- After that, save the code and go back to your worksheet.
- Next, select Cell E5 and insert the following formula.
=ConcatenateSales(C5:C11,",")- Now, press Enter to get all the sales of 2019 with a separator.
- Similarly, to concatenate the values of Column D, insert the following formula in Cell E6 and press Enter.
=ConcatenateSales(D5:D11,",")- Thus, you can employ a user-defined function to concatenate range with a separator.
Things to Remember
- In all the codes of this article, we’ve used the Addition symbol (+) to concatenate two strings.
- But you can also use the Ampersand symbol (&) to concatenate two strings in VBA.
Practice Section
In this section, we are giving you the dataset to practice on your own and learn to use these methods.
Conclusion
Using these methods, you can concatenate a range of cells with VBA in Excel. You can concatenate the rows within a range, the columns within a range, and also, both the rows and columns within a range, according to your need. Do you have any questions? Feel free to ask us. And visit ExcelDemy for many more articles like this. Thank you!
