How to Concatenate Strings in VBA (With Examples)


You can use the following methods to concatenate strings in VBA:

Method 1: Concatenate Two Strings

Sub ConcatStrings()
    Range("C2") = Range("A2") & Range("B2")
End Sub

This example will concatenate the strings in cells A2 and B2 and display the result in cell C2.

Method 2: Concatenate Two Strings with Delimiter

Sub ConcatStrings()
    Range("C2") = Range("A2") & " " & Range("B2")
End Sub

This example will concatenate the strings in cells A2 and B2 with a space in between the strings and display the result in cell C2.

Method 3: Concatenate Two Columns with Delimiter

Sub ConcatStrings()
    Dim i As Integer

    For i = 2 To 6
      Cells(i, 3).Value = Cells(i, 1) & "_" & Cells(i, 2)
    Next i
End Sub

This example will concatenate the strings in ranges A2:A6 and B2:B6 with an underscore in between the strings and display the results in cells C2:C6.

The following examples show how to use each of these methods in practice.

Example 1: Concatenate Two Strings

We can create the following macro to concatenate two strings:

Sub ConcatStrings()
    Range("C2") = Range("A2") & Range("B2")
End Sub

When we run this macro, we receive the following output:

Notice that the strings in cells A2 and B2 have been concatenated together into cell C2.

Example 2: Concatenate Two Strings with Delimiter

We can create the following macro to concatenate two strings with a space as a delimiter:

Sub ConcatStrings()
    Range("C2") = Range("A2") & " " & Range("B2")
End Sub

When we run this macro, we receive the following output:

Notice that the strings in cells A2 and B2 have been concatenated together with a space in between them and the result is shown in cell C2.

Example 3: Concatenate Two Columns with Delimiter

We can create the following macro to concatenate the strings in two columns with an underscore as a delimiter:

Sub ConcatStrings()
    Dim i As Integer

    For i = 2 To 6
      Cells(i, 3).Value = Cells(i, 1) & "_" & Cells(i, 2)
    Next i
End Sub

When we run this macro, we receive the following output:

Notice that the strings in the range A2:A6 and B2:B6 have been concatenated together with an underscore in between them and the results are shown in the range C2:C6.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Count Occurrences of Character in String
VBA: How to Check if String Contains Another String
VBA: How to Count Cells with Specific Text

Leave a Reply

Your email address will not be published. Required fields are marked *