How to Concatenate in VBA

This tutorial explains how to concatenate strings in Excel using VBA code.

You can download the following dataset to practice.

We can concatenate (combine) strings using the ampersand & operator.

1. Concatenate Two Strings

In the following code, we have used & operator to combine strings - "My name" and "is Lucifer.".

Sub Concatenate2strings()
    Dim str1 As String
    Dim str2 As String
    Dim result As String
    
    str1 = "My name"
    str2 = "is Lucifer."
    
    result = str1 & " " & str2

    MsgBox result 
End Sub

Press Run or F5 to run the above macro.

We use " " to add a space between two strings.

2. Concatenate Multiple Columns

The following code can be used to concatenate the values stored in columns A and column B and copy the final output to column C :

Sub ConcatenateRangeValues()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
        lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        
    
    For i = 2 To lastRow
       ws.Cells(i, 3).Value = ws.Cells(i, 1).Value & " " & ws.Cells(i, 2).Value
    Next i
End Sub

Press Run or F5 to run the above macro.

About Author:
Yash Gaur

Yash is pursuing an MBA in Finance with a keen interest in analytics. He enjoys working with data and leveraging his research and analytical skills to generate valuable insights.

Let's Get Connected Email LinkedIn