All About Concatenate in Excel and VBA
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.
- Simple string concatenate
- String concatenate with delimiter (2 methods)
- Range concatenate
- Handling English grammar during string concatenate
- Building a custom VBA function to concatenate (2 methods)
Contents
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.
One thought on “All About Concatenate in Excel and VBA”