How to Concatenate Strings in Excel VBA?

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,

Code:

MsgBox "Merge" & "Text"

Concatenate strings in Cells

To concatenate strings in cells,

Code:

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

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,

Code:

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.

Code:

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