In this post, you will learn how to concatenate strings in your Excel spreadsheet programmatically using Excel VBA.
Concatenate Strings in Excel VBA
Concatenate strings is merging or adding two or more texts together. In excel VBA, we can concatenate strings and the strings in the cells. Concatenation can be performed using the following methods.
- Using the & Operator with Spaces
- Using the & Operator to Concatenate a Quotation Mark
- Putting Strings on a New line
Concatenate Strings in MsgBox
To concatenate strings in a MsgBox,
MsgBox "Merge" & "Text"
Concatenate strings in Cells
To concatenate strings in cells,
Range("C1").Value = Range("A1").Value & Range("B1").value
When you need to perform the same operation with a variable declaration, use the below code
Sub ConcatenateStrings() Dim FirstString as String Dim SecondString as String FirstString = Range("A1").Value SecondString = Range("B1").Value Range("C1").Value = FirstString & SecondString End Sub
Using the & Operator with Spaces
To concatenate strings using the & operator with spaces,
Sub Merge_Text() Dim FirstString As String Dim SecondString As String Dim Result As String FirstString = "Concatenate" SecondString = "Strings" Result = FirstString & " " & SecondString MsgBox Result End Sub
Using the & Operator to Concatenate a Quotation Mark
Let’s imagine when you need to concatenate a string and a quotation mark. Here is the code to concatenate strings and quotation mark.
Sub StringWithQuotation() Dim FirstString As String Dim SecondString As String Dim Result As String StringOne = "String concatenation with quotation mark" StringTwo = """" StringThree = FirstString & " " & SecondString MsgBox Result End Sub