VBA Concatenate

VBA concatenates one of those things we used to combine two or more value cell values. So if we say it in simple language, it is combined. It is joining two or more values together to have full value.

We have a function called CONCATENATE in excel, which will combine two or more values or two or more cell values.

But in VBA, we do not have any built-in function to concatenate two or more values together. We do not even get to access the worksheet function class to access the VBA CONCATENATE function as a worksheet function.

Suppose we do not have any built-in function to concatenate values and even do not integrate the worksheet function with VBA. Now the challenge is, how do we combine values?

Even though there are no built-in functions, we can still combine them in VBA using the “ampersand” (&) symbol.

If you follow our posts regularly, we often use the ampersand (&) symbol in our coding.

For example, if you have the first and last names separately, we can combine these two and make them a full name. Then, follow the below steps to write the VBA macro code on our own.

Step 1: Go to Visual Basic Editor and create a VBA sub procedure.

Step 2: Define three variables as String.

Code:

Sub Concatenate_Example()

  Dim First_Name As String
  Dim Last_Name As String
  Dim Full_Name As String

End Sub

Step 3: Now, assign the variable's first and last names.

Code:

Sub Concatenate_Example()

  Dim First_Name As String
  Dim Last_Name As String
  Dim Full_Name As String

  First_Name = "Sachin"
  Last_Name = "Tendulkar"

End Sub

Step 4: Now, combine these two names to the variable Full_Name using the ampersand variable.

Code:

Sub Concatenate_Example()

  Dim First_Name As String
  Dim Last_Name As String
  Dim Full_Name As String

  First_Name = "Sachin"
  Last_Name = "Tendulkar"

  Full_Name = First_Name & Last_Name

End Sub

Step 5: Now, show the value of the variable Full_Name in the message box.

Code:

Sub Concatenate_Example()

  Dim First_Name As String
  Dim Last_Name As String
  Dim Full_Name As String

  First_Name = "Sachin"
  Last_Name = "Tendulkar"

  Full_Name = First_Name & Last_Name

  MsgBox Full_Name

End Sub

Now, run the code. We will get the full name in the message box.

The problem with this full name is we have not added a first name and last name separator character space. Therefore, combine space characters while combining the first and last names.

Code:

Sub Concatenate_Example()

  Dim First_Name As String
  Dim Last_Name As String
  Dim Full_Name As String

  First_Name = "Sachin"
  Last_Name = "Tendulkar"

  Full_Name = First_Name & " " & Last_Name

  MsgBox Full_Name

End Sub

It will give a proper full name now.

Like this, using the ampersand symbol, we can concatenate values. Now, we will solve the worksheet problem of solving first name and last name together to make it a full name.

Since we need to combine many names, we need to use loops to combine the first and last names. The below code will do the job for you.

Code:

Sub Concatenate_Example1()

  Dim i As Integer

  For i = 2 To 9
    Cells(i, 3).Value = Cells(i, 1) & " " & Cells(i, 2)
  Next i

End Sub

It will combine the first and last names, just like our VBA concatenate function.

If you notice my codes, we have added a space character between values and an ampersand symbol. It is essential because of the nature of VBA programming.

We cannot combine values and ampersand symbols. Otherwise, we will get a "Compile error" like the one below.

In VBA, we can use the JOIN function to combine values. First, look at the VBA JOIN function syntax.

• The array is nothing but an array that holds our values—for example, both first name & last name.
• The delimiter is nothing but what is the separator between each array value, in this case, space character.

The below code will show the example of the same.

Code:

Sub Concatenate_Example2()

  Dim MyValues As Variant
  Dim Full_Name As String

  MyValues = Array("Sachin", "Tendulkar")
  Full_Name = Join(MyValues, " ")

  MsgBox Full_Name

End Sub