Table of Contents
VBA provides an easy way to concatenate strings using the & operator. To concatenate two strings, simply place the & operator between them. For example, “Hello” & “World” will return the string “HelloWorld”. You can also store the concatenated string in a variable for further use. You can also use the VBA function CONCATENATE() to concatenate strings. This function takes up to 255 strings and returns them as one string.
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.