How to Concatenate Range with Separator Using VBA in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

excel vba concatenate range with separator


 


How to Concatenate Range with Separator Using VBA in Excel: 4 Easy Ways

Here we’ve got a data set with the names of some products and their sales for 2 years of a company called Mars Group.

Dataset to concatenate range with separator

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.

Selecting Visual Basic from Developer tab

  • Now, the Microsoft Visual Basic for Application box will open.
  • After that, click on Insert >> select Module.

Microsoft Visual Basic Editor Window

  • 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 for concatenating rows with separator

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.

Saving Code

  • After that, select cell range C5:D11.
  • Further, go to the Developer tab >> click on Macros.

Selecting cell range to concatenate rows

  • Now, the Macros dialog box will appear.
  • Then, select Concatenate_Rows_with_Seperator.
  • Lastly, click on Run.

Macro dialog box

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

Entering separator

  • Finally, you’ll get the sales record of each product concatenated in a column right to the selected range.

Output after concatenating rows with separator

Read More: Excel VBA to Concatenate Cell Values 


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 for Concatenating column values

Code Breakdown

  • Firstly, we created a Sub Procedure as Join_Columns.
  • Then, we inserted an InputBox to enter a separator.
  • Next, we used the 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.

Selecting cell range to concatenate column values

  • Then, select the macro named Join_Columns.
  • Now, click on Run.

Selecting macro named “Join_Columns”

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

Input separator

  • Thus, you’ll find the sales of all the products in each year concatenated in a column right to your data set.

Output after concatenating columns with separator

Read More: Macro to Concatenate Multiple Columns in Excel


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 for Concatenating both row and column values

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.

Selecting cell range to concatenate row and column values

  • Now, select Concatenate_Rows_and_Columns macro.
  • Next, click on Run.

Selecting for named “Concatenate_Rows_and_Columns”

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

Input separator

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

Insert cell reference

  • Finally, you’ll get the selected range of cells merged into a single cell in your desired cell location (Cell C13 in this example).

Output after concatenating rows and columns with separator

Read More: How to Concatenate String and Integer using VBA


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

Creating user defined function

Code Breakdown

  • To start with, we created a user-defined function as ConcatenateSales with the 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.

Insert formula to concatenate values in Column C

Here, we inserted cell range C5:C11 as cell_range1 and comma (,) as separator1.
  • 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.

Insert formula to concatenate values in Column D

Read More: How to Concatenate String and Variable in Excel VBA


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.

Practice Sheet


Download Practice Workbook

You can download the workbook to practice yourself.


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.

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.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo