How to Use Concatenate in Excel VBA (4 Methods)

Download the Practice Workbook

Download this practice workbook to exercise while you are reading this article.

VBA Concatenate Function.xlsm

Introduction to the VBA Concatenate Function

⟴ Syntax

String1 = “First Text”

String2 = “Second Text”

⟴ Return Value

Return_value = String1 & String2

4 Different Uses of VBA Concatenate Function in Excel

Method 1 – Use the Ampersand (&) Operator to Join Cells in VBA Concatenate

We have a two-column dataset with first names in one column and last names in the other. By merging the two columns, we can get the full names.

Steps:

Press Alt + F11 to open the VBA code window.
Click Insert and select Module.

Copy and paste the following VBA code into the module.

Sub Concatenate2()
Dim String1 As String
Dim String2 As String
Dim full_string As String
String1 = Cells(5, 2).Value
String2 = Cells(5, 3).Value
Cells(5, 5).Value = String1 & String2
MsgBox (full_string)
End Sub

Here,

String1 = Cells(5, 2).Value is the first cell location B5, row 5, and column 2.
String2 = Cells(5, 3).Value is the second cell location C5, row 5, and column 3.
Cells(5, 5).Value = String1 & String2 is the result cell location E5, row 5 and column 5.
String1 & String2 are the two strings joined by the ampersand (&)

Save and press F5 to run the program.

Follow and repeat the steps for the other cells (changing the cell locations in the code).

Note. To run VBA code, every time make sure your Excel file is saved in Excel Macro-Enabled Worksheet (xlsm.) format.

Method 2 – Use the Plus (+) Operator to Join Cells in VBA Concatenate

Let’s use the same dataset as before.

Steps:

Press Alt + F11 to open the VBA window.
Click Insert and select Module.
Paste the following VBA code in the module:

Sub Concatenate2()
Dim String1 As String
Dim String2 As String
Dim full_string As String
String1 = Cells(5, 2).Value
String2 = Cells(5, 3).Value
Cells(5, 5).Value = String1 + String2
MsgBox (full_string)
End Sub

Here,

Cells(5, 5).Value = String1 + String2 is this line we use plus (+) sign instead of the ampersand (&)

Save and press F5 to run the program.

Repeat the process by changing the cell positions inside the code (i.e., changing the row number).

Similar Readings:

Method 3 – Add Multiple Columns Using VBA Concatenate

In the previous two approaches, we combined two cells. If we want to apply that to the entire column, adding one by one will take a long time. Let’s use column-wide code to streamline the process.

Steps:

Press Alt+ F11 to open the VBA window and Insert a new Module.
Paste in the following VBA code:

Sub ConcatCols()
'concatenate columns B & C in column E
Dim LastRow As Long
With Worksheets("Sheet3")
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
With .Range("E5:E" & LastRow)
.Formula = "=B5&C5"
.Value = .Value
End With
End With
End Sub

Here,

With Worksheets(“Sheet3”) is your current worksheet name.
LastRow = .Cells(.Rows.Count, “B”).End(xlUp).Row is the first column name.
With .Range(“E5:E” & LastRow) is the result return cell range.
.Formula = “=B5&C5” is the formula to join the first cell of the range.

Save and press F5 to run the program.

Method 4 – Join Multiple Rows Using VBA Concatenate

Let’s add three cells into one this time.

Steps:

Press Alt+ F11 to open the VBA window and Insert a new Module.
Paste in the following VBA code:

Sub vba_concatenate()
Dim rng As Range
Dim i As String
Dim SourceRange As Range
Set SourceRange = Range("B5:D5")
For Each rng In SourceRange
i = i & rng & " "
Next rng
Range("B8").Value = Trim(i)
End Sub

Here,