VBA Concatenate

Concatenation means joining two values or two strings together, similar to excel we use & or also known as ampersand operator to concatenate, two concatenate two strings we use & operator like String 1 & String 2, now there is an important thing to remember and that is while using the & operator we need to provide spaces or VBA will consider it as long.

VBA Concatenate Strings

VBA Concatenate is one of those things which we used to combine two or more value cell values together. If I say in simple language it is combining, it is joining two or more values together to have full value.

We have a function called CONCATENATE in excelCONCATENATE In ExcelThe concatenate function in Excel is used to connect or concatenate two or more characters, strings, or numbers. The concatenate function is a better alternative to using the & operator to connect two variables.read more, which will do the combining together two or more values or two or more cell values together.

But in VBA, we don’t have any built-in function to concatenate two or more values together. In fact, we don’t even get to access the worksheet function class to access the VBA CONCATENATE function as a worksheet function.

VBA Concatenate

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Concatenate (wallstreetmojo.com)

How to Concatenate Strings in VBA?

If we don’t have any sort of built-in function to concatenate values and even the worksheet function is not integrated with VBA. Now the challenge is, how do we combine values?

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

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

For example, if you have the first name and last name separately, we can combine these two and make it as a full name. 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 procedureCreate A VBA Sub ProcedureSUB in VBA is a procedure which contains all the code which automatically gives the statement of end sub and the middle portion is used for coding. Sub statement can be both public and private and the name of the subprocedure is mandatory in VBA.read more.

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 first name and last name to the 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"

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.

VBA Concatenate Example 1

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

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

This will give a proper full name now.

VBA Concatenate Example 1-1

Like this using the ampersand symbol, we can concatenate values. Now we will solve the worksheet problem of solving First Name & Last Name together to make it a full name.

Example 2

Since we need to combine many names, we need to use loops to combine the first name & last name. 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

This will combine the first name and last name, just like our VBA concatenate function.

Example 2-1

Common Mistake in Ampersand VBA Concatenation

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

We cannot combine values and ampersand symbols together; otherwise, we will get Compile Error like the below.

Example 2-2

VBA Concatenate Using JOIN Function

In VBA, we can use the JOIN function to combine values. First, look at VBA JOINVBA JOINIn VBA, we use the Join command to join two or more strings together, similar to the concatenate function and the "&" command. read more function syntax.

VBA Join Formula
  • 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

Recommended Articles

This has been a guide to VBA Concatenate. Here we learned how to use VBA Concatenate Function using the Join function along with some practical examples and a downloadable excel template. Below are some useful excel articles related to VBA –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>