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