VBA Concatenate Strings

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 excelCONCATENATE In ExcelThe CONCATENATE function in Excel helps the user concatenate or join two or more cell values which may be in the form of characters, strings or numbers.read more, 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.

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

How to Concatenate Strings in VBA?

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 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 variable’s first and last names.

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.

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.

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.

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.

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.

Common Mistake in Ampersand VBA Concatenation

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.

VBA Concatenate Using JOIN Function

In VBA, we can use the JOIN function to combine values. First, look at the 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.

  • 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.

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

This article has been a guide to VBA Concatenate. Here, we learned how to use the Join function’s VBA concatenate function, some practical examples, and a downloadable Excel template. Below are some useful Excel articles related to VBA: –

  • VBA LENConcatenate Columns in ExcelExcel VBA Worksheet FunctionConcatenate Date in Excel