VBA Concatenate Text Strings Together (& – Ampersand)

We have already gone over an introduction to string functions in our VBA Strings and Substrings Functions tutorial. We are now going to look at how to concatenate text strings.

Concatenate Strings

You can use the & operator in VBA to join text strings.

MsgBox "Merge" & "Text"

Concatenate Cells

You can also concatenate cells together. Below, we have the text strings in A1 and B1:

The following code shows you how to join text strings from cell A1 and B1 using the & operator,  in cell C1:

Range("C1").Value = Range("A1").Value & Range("B1").value

The result is:

Concatenate Variables

This is the full procedure to concatenate two cells together using string variables.

Sub ConcatenateStrings()

Dim StringOne as String
Dim StringTwo as String

StringOne = Range("A1").Value
StringTwo = Range("B1").Value 

Range("C1").Value = StringOne & StringTwo

End Sub

 

Using the & Operator with Spaces

When you want to include spaces you use & in conjunction with ” “. The following code shows you how you would include spaces:

Sub ConcatenatingStringsWithSpaces()

Dim StringOne As String
Dim StringTwo As String
Dim StringThree As String

StringOne = "This is"
StringTwo = "the text"
StringThree = StringOne & " " & StringTwo

MsgBox StringThree
End Sub

The MessageBox result is:

Using the & Operator to Concatenate a Quotation Mark

Let’s say your text string contains a quotation mark, the following code shows you how to include a quotation mark within a text string:

Sub ConcatenatingAQuotationMark()

Dim StringOne As String
Dim StringTwo As String
Dim StringThree As String

StringOne = "This is the quotation mark"
StringTwo = """"
StringThree = StringOne & " " & StringTwo

MsgBox StringThree

End Sub

The result is:

Putting Strings on a New line

Let’s say you have five text strings, you can put each text string on a new line or paragraph, using either the vbNewLine, vbCrLf, vbCr or Chr Function. The following code shows you how to put each text string on a new line:

Sub PuttingEachTextStringOnANewLine()

Dim StringOne As String
Dim StringTwo As String
Dim StringThree As String
Dim StringFour As String
Dim StringFive As String


StringOne = "This is the first string"
StringTwo = "This is the second string"
StringThree = "This is the third string"
StringFour = "This is the fourth string"
StringFive = "This is the fifth string"

MsgBox StringOne & vbNewLine & StringTwo & vbCrLf & StringThree & vbCr & StringFour & Chr(13) & StringFive

End Sub

The result is: