In Excel, text concatenation is a common task which can be accomplished easily with the CONCATENATE function or with the “&” operator.

For example, to concatenate “AB”, “CD”, “EF”, we can use Excel formula:

=CONCATENATE("AB","CD","EF")

or

="AB" & "CD" & "EF"

In this tutorial, we are going to learn about how to concatenate strings or values in Excel VBA in different scenarios. In addition to achieving simple concatenate like the example above, we are also going to explore more powerful text concatenate methods with VBA.

1. Simple string concatenate
2. String concatenate with delimiter (2 methods)
3. Range concatenate
4. Handling English grammar during string concatenate
5. Building a custom VBA function to concatenate (2 methods)
   
   

Simple String Concatenate

Let’s being our first VBA scenario with the use of “&” as the connector of strings.

Sub Concat1()
Dim tmp As String		'declare variable

tmp = "AB" & "CD" & "EF"	'concatenate strings

MsgBox tmp			'display result in message box

End Sub

Using “+” instead of “&”?

For those who have experience with other languages (such as Python), you may be used to using “+” to connect strings. This is also possible in VBA. However, as a good practice in VBA, I would suggest using “&” always to concatenate because “+” will normally be used for addition of numeric values.

Example	Returns	Comment
“a” + “b”	ab	As the inputs are strings, the “+” operator connects the strings like “&”. This is not a good VBA coding practice.
a” & “b”	ab	Normal text concatenate.
1 & 3	13	With the use of “&”, the numbers 1 and 3 are treated by VBA as strings.
“1” + “3”	13	“1” and “3” in double quotes means they’re strings. Therefore text concatenate is performed. This is not a good VBA coding practice. Should use “&” to avoid confusion in the code.
1 + 3	4	1 and 3 are added with the “+” math operator.

String Concatenate with Delimiter (Two Methods)

Sometimes we connect strings to form a phrase or a sentence. In such cases, we want to concatenate strings with delimiters (such as a space) inserted between each string.

For example:

We want to concatenate the 4 strings/values: “We”, “have”, “1” and “car” to return a sentence “I have 1 car.”

Method 1

Similar to the previous scenario 1, we can use “&” to concatenate, and add a space ” ” (double quotes with a space in the middle) between each word:

Sub Concat_delimiter1()
Dim tmp As String		'declare a variable to hold the result
'concatenate strings with space delimiter
tmp = "We" & " " & "have" & " " & 1 & " " & "car" & "."
MsgBox tmp		'display result in message box
End Sub

Method 2

Alternately, we can use the function “JOIN” to connect strings/values with an array.

In the macro below, in line 3, the “JOIN” function is used. The 4 values to concatenate are put into an Array. Then, the delimiter of a space is place in the last argument of the function.

Sub Concat_delimiter2()
Dim tmp As String		'declare a variable to hold the result
tmp = Join(Array("We", "have", 1, "car"), " ") 
tmp = tmp & "."	'append a period at the end of sentence
MsgBox tmp		'display result in message box
End Sub

In line 4, a final touch of the result by adding a period to the end of the variable.

Range Concatenate

How about when input strings are in a row of cells? Let’s look at the range below.

First, we want to concatenate the values in range A3:D3. We can do it with a FOR-NEXT loop.

Sub Concat_range1()
'declare a variable to hold the result
Dim tmp As String

'declare a Range variable for looping through each cell
Dim cell As Range

'use a FOR-NEXT loop to go through every cell in the range
For Each cell In ActiveSheet.Range("A3:D3")
    tmp = tmp & " " & cell.Value
Next

'remove unwanted leading space, and add a period at end
tmp = Trim(tmp) & "."

MsgBox tmp	'display result

End Sub

Next, let’s write a macro to concatenate each row in A3:D5, and put the result in column E.

Our expected result will look like this:

We extend the previous macro with a nested FOR-NEXT loop. The first FOR-NEXT loop loops through each row in the range. The second loop loops through each cell in each row.

Sub Concat_range2()
'declare a variable to hold the result
Dim tmp As String

'declare a Range variable for looping through each row in range
Dim r As Range

'declare a Range variable for looping through each cell in a row
Dim cell As Range

'use a FOR-NEXT loop to go through every row in the range
For Each r In ActiveSheet.Range("A3:D5").Rows
    tmp = ""		'reset result variable
    'use a FOR-NEXT loop to go through every cell in the range
    For Each cell In r.Cells
        tmp = tmp & " " & cell.Value
    Next
	'remove unwanted leading space, and add a period at end
    tmp = Trim(tmp) & "."   

	'place the result into the cell next to the last cell of row
    r.Cells(r.Cells.Count).Offset(0, 1).Value = tmp
Next
End Sub

Handling English Grammar During String Concatenate

You may have observed that the result in the last example misses one thing: treatment of plural when number of cars is more than 1. This is a very common scenario and is quite easy to resolve. An additional variable called “grammar” is declared in the macro below to handle the grammar.

Sub Concat_range3()
'declare a variable to hold the result
Dim tmp As String

'declare a Range variable for looping through each row in range
Dim r As Range

'declare a Range variable for looping through each cell in a row
Dim cell As Range

Dim grammar as string	'for treatment of grammar

'use a FOR-NEXT loop to go through every row in the range
For Each r In ActiveSheet.Range("A3:D5").Rows
    tmp = ""		'reset result variable
    grammar = ""	'reset grammar variable
    'use a FOR-NEXT loop to go through every cell in the range
    For Each cell In r.Cells
        tmp = tmp & " " & cell.Value
    Next
    
    'treatment of plural when the 3rd cell is >1
    If r.Cells(3).Value > 1 Then grammar = "s"

    'remove unwanted leading space, grammar treatment, add period at end
    tmp = Trim(tmp) & grammar & "."

    'place the result into the cell next to the last cell of row
    r.Cells(r.Cells.Count).Offset(0, 1).Value = tmp
Next
End Sub

Building a Custom VBA Function to Concatenate

The macro examples so far showed you how to concatenate strings within a VBA subroutine. How about writing a VBA custom function to concatenate?  The advantage of writing a VBA function is that you may use such function in your Excel sheet.

Let’s look at the two examples below.

Method 1: Custom VBA Function to concatenate two input strings:

'accepts 2 input values, optional definition of delimiter
Function CONCAT1(input1 As String, input2 As String, _
	Optional delimiter As String = "") As String

CONCAT1 = input1 & delimiter & input2

End Function

The function CONCAT1 accepts 2 input values (can be string or numeric). It also has an optional input of delimiter, which by default is empty. You can use this function in an Excel sheet like in the picture shown below. See the function in cell C3:

Method 2: Custom VBA Function to concatenate cells in a range input:

The function CONCAT2 accepts a range input. It has an optional input of delimiter, which by default is empty.

Function CONCAT2(range1 As Range, _
	Optional delimiter As String = "") As String
Dim tmp As String
Dim r As Range
Dim cell As Range

    'use a FOR-NEXT loop to go through every cell in the range
    For Each cell In range1
        tmp = tmp & delimiter & cell.Value
    Next
    
    If Left(tmp, 1) = delimiter Then tmp = Right(tmp, Len(tmp) - 1)
    CONCAT2 = tmp
    
End Function

You can use this function CONCAT2 in an Excel sheet like in the picture shown below. See the function in cell F3:

We have gone through a number of different scenarios step-by-step on how to concatenate strings in VBA. As text concatenate is one of the core skills in VBA programming, you’ll find the techniques you learnt here very useful in your own VBA projects.