Concatenation is the joining of two or more strings together. VBA Tool can also be used for other string functions to concatenate two or more strings in Excel. In this article, we shall discuss concatenating strings using the VBA Tool.
Concatenate Cells in Excel
Steps to follow:
1. Open the Excel application.
2. Open the workbook with the cells you want to concatenate. For example, let's use cells A1 and A2.
3. Click on the Developer tab on the Ribbon, and then locate the Visual Basic button.
4. In the Visual Basic screen, click the Insert tab on the Ribbon and select the Module button.
5. Type the following code in the empty module.
Sub ConcatenateStrings()
Dim StringOne As String
Dim StringTwo As String
StringOne = Range("A1").Value
StringTwo = Range("A2").Value
Range("C1").Value = StringOne & StringTwo
End Sub
NOTE:
You should initialize the cells with your dataset. For example, StringOne = Range("A1") from the above code we have.Value and StringTwo = Range("A2").Value
Then, you should set the cell which will hold the execution result. From the above code, we have Range("C1").Value.
Finally, concatenate the two cell variables using the Ampersand (&) operator.
6. Finally, hit the F5 button to run the code.
Concatenate Cells with spaces
Steps:
1. Open the workbook with the cells you want to concatenate. For example, let's use cells A1 and A2.
2. Click on the Developer tab on the Ribbon, and then locate the Visual Basic button.
3. In the Visual Basic screen, click the Insert tab on the Ribbon and select the Module button.
4. Type the following code in the empty module.
Sub ConcatenateStrings()
Dim StringOne As String
Dim StringTwo As String
StringOne = Range("A1").Value
StringTwo = Range("A2").Value
Range("C1").Value = StringOne & “ ” &StringTwo
End Sub
NOTE:
You will see quotation marks between the two Ampersand (&) operators.
5. Finally, hit the F5 button to run the code.
Concatenate and have strings on a new line
Steps:
1. Click on the Developer tab on the Ribbon, and then locate the Visual Basic button.
2. In the Visual Basic screen, click the Insert tab on the Ribbon and select the Module button.
3. Type the following code in the empty module.
Sub EachTextStringOnANewLine()
Dim StringOne As String
Dim StringTwo As String
Dim StringThree As String
Dim StringFour As String
Dim StringFive As String
StringOne = " String 1"
StringTwo = " String 2"
StringThree = " String 3"
StringFour = " String 4"
StringFive = " String 5"
MsgBox StringOne & vbNewLine & StringTwo & vbCrLf & StringThree & vbCr & StringFour & Chr(13) & StringFive
End Sub
NOTE:
You should initialize the strings. For example, Dim StringOne As String
4. Finally, hit the F5 button to run the code.
Concatenate Numbers
Steps:
1. Click on the Developer tab on the Ribbon, and then locate the Visual Basic button.
2. In the Visual Basic screen, click the Insert tab on the Ribbon and select the Module button.
3. Type the following code in the empty module.
Sub VBA_Concatenate()
Dim MyString As String
Dim str1 As String
Dim str2 As String
Dim str3 As String
str1 = "18"
str2 = "06"
str3 = "29"
MyString = str1 & str2 & str3
End Sub
4. Finally, hit the F5 button to run the code.