VBA Concatenate

What is VBA Concatenate Strings?

VBA Concatenate Strings refers to the process of combining multiple strings or variables to create a single string using Visual Basic for Applications (VBA) in Excel. It allows you to dynamically generate text by merging different components.

This technique is beneficial in Excel macros when creating customized messages, building dynamic formulas, or generating reports. Let us look at an example where we must create an invoice code by combining a prefix and a numeric invoice number. Consider a scenario where the prefix is ‘INV,’ and the invoice number is ‘12345’. We want to concatenate these two components to create the invoice code.

By combining the prefix ‘INV’ with the invoice number ‘12345’ using the ampersand (&) operator, we can generate the invoice code ‘INV-12345’.

VBA Concatenate Intro

This code demonstrates how VBA concatenation allows us to merge different parts of a string to create unique identifiers or codes in Excel macros.

VBA Concatenate Intro - Output
Key Takeaways
  1. VBA Concatenate Strings allows you to combine and manipulate text in Excel macros.
  2. Use the ampersand (&) operator to concatenate strings, variables, or cell values. Remember to convert non-string values to strings before concatenation.
  3. We can combine the contents of multiple cells into a single cell by utilizing VBA Concatenate cells’ function.
  4. Handle empty or null values, consider performance implications, and remove unnecessary spaces or characters from the concatenated result.
  5. VBA Concatenate function provides flexibility and customization to Excel macros, enabling dynamic text generation and advanced data manipulation.

How to Concatenate Strings in VBA?

To concatenate strings in VBA, follow these steps:

Step 1: Declare variables (if necessary). Before concatenating strings, declare variables to store the strings or values you want to combine. For example:

Dim str1 As String
Dim str2 As String
Dim result As String

Step 2: Set the values of the variables you want to concatenate. For example:

str1 = “Hello”
str2 = “World”

Step 3: Concatenate the strings: Use the ampersand (&) operator to combine the strings and assign the result to a variable.

result = str1 & str2

Step 4:  

  • Display the concatenated string using message boxes.
  • Write it to a cell.
  • Use it in any desired way.

For example:

MsgBox result

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.


Let us look at a few examples to explain the concatenation of strings in VBA.

Example #1: Concatenating strings and variables:

In this example, we will explore how to use VBA concatenate string and variable. We will combine textual content with variable values to create meaningful and dynamic messages.

Step 1: Open Microsoft Excel and navigate to the Developer tab. Click on the “Visual Basic” button to access the VBA Editor.

VBA Concatenate - Example 1 - Step 1

Step 2: In the VBA Editor, right-click on the project explorer window and select “Insert” followed by “Module.” It will create a new module where you can write your VBA code.

VBA Concatenate - Example 1 - Step 2

Step 3: Start by defining a new Sub procedure within the module. In this case, the Sub procedure is named “ConcateNameandAge.” It is the code’s entry point and where the execution will begin.

VBA Concatenate - Example 1 - Step 3

Step 4: Declare the variables that will be used in the code. In this example, three variables are declared: “name” as a String, “age” as an Integer, and “message” as a String. These variables will store the name, age, and concatenated message.

VBA Concatenate - Example 1 - Step 4

Step 5: Assign values to the variable’s “name” and “age.” In this code snippet, the name is assigned the value “John,” and the age is given the value 30. These are just sample values used for demonstration purposes.

VBA Concatenate - Example 1 - Step 5

Step 6: Use the concatenation operator (&) to combine string literals and variable values into the “message” variable.

The code concatenates the strings “My name is, “the value of the variable “name,” the string,” and I am, “the value of the variable “age,” and the string ” years old.” The resulting concatenated string is stored in the “message” variable.

VBA Concatenate - Example 1 - Step 6

Step 7: Save the module and close the VBA. Now press Alt + F8 to open the Macro menu, select “ConcateNameandAge,” and run the macro.

VBA Concatenate - Example 1 - Step 7

Step 8: After running the code, you will encounter a message: “My name is John, and I am 30 years old.”

VBA Concatenate - Example 1 - Step 8

Here is the complete code:

Sub ConcateNameandAge()
Dim name As String
Dim age As Integer
Dim message As String
name = “John”
age = 30
message = “My name is ” & name & ” and I am ” & age & ” years old.”
MsgBox message
End Sub

Example #2: Concatenating range values

In this example, we will explore using the VBA concatenate range. Combining the values from multiple cells within a range can create a single string representing the consolidated data.

Step 1: In the new module, start by creating a Subroutine “ConcatRange().”

Example 2 - Step 1

Step 2: Here, we declare three variables: “rng” as a Range object to represent the range of cells to concatenate, “cell” as a Range object to iterate over each cell in the range, and “result” as a String variable to store the concatenated result.

Example 2 - Step 2

Step 3: Here, we set the “rng” variable to refer to the range of cells from A1 to A3. It is the range that will be concatenated.

Example 2 - Step 3

Step 4: In this step, we create an iteration over each Cell in the Range. This line starts a loop that iterates over each cell in the “rng” range.

Example 2 - Step 4

Step 5: Within the loop, the code concatenates the value of each cell in the range to the “result” variable.

The ampersand (&) operator concatenates the cell value with the existing content of the “result” variable. A comma and a space are added after each value for formatting purposes.

VBA Concatenate - Example 2 - Step 5

Step 6: This line removes the trailing comma and space from the “result” variable after the loop. The Left excel function extracts a substring from the “result” variable, excluding the last two characters. The code then displays the concatenated result in a message box using the MsgBox function.

Example 2 - Step 6

Step 7: Save the module and close the VBA. Now press Alt + F8 to open the Macro menu, select “ConcatRange,” and run.

Example 2 - Step 7

Step 8: Upon executing the code, you will observe that the data in cells A1 to A3 are concatenated and displayed in the message box in the desired format, such as “Apple, Banana, Orange.”

Example 2 - Step 8

Here is the complete code:

Sub ConcatRange()
Dim rng As Range
Dim cell As Range
Dim result As String
Set rng = Range(“A1:A3”)
For Each cell In rng
result = result & cell.Value & “, “
Next cell
result = Left(result, Len(result) – 2) ‘ Remove the trailing comma and space
MsgBox result
End Sub

Common Mistake in Ampersand VBA Concatenation

One common mistake when using the ampersand (&) operator for VBA concatenation is forgetting to convert non-string values to strings. You may encounter unexpected results or runtime errors if you attempt to concatenate a non-string value directly.

To avoid this mistake, you must ensure that non-string values are converted to strings before concatenating them. You can convert non-string values to strings using the “CStr()” function.

Here’s an example:

Dim num As Integer
Dim str As String
num = 42
str = “The answer is: ” & CStr(num)

Using “CStr(),” the integer value 42 is converted to a string before concatenation.

VBA Concatenate Using JOIN Function

In addition to the ampersand operator, VBA provides the JOIN function for concatenating an array of strings using a specified delimiter. The syntax of the JOIN function is:

result = Join(array, delimiter)

The “array” parameter represents the array of strings you want to concatenate, and the “delimiter” parameter specifies the character or string to be used as a separator between the concatenated elements.

Here’s an example:

Dim fruits() As String
Dim result As String
fruits = Array(“Apple”, “Banana”, “Orange”)
result = Join(fruits, “, “)
MsgBox result

The array “fruits” contains three elements, and the JOIN function concatenates them using a comma and space as the delimiter. The resulting string is displayed in a message box: “Apple, Banana, Orange”.

Important Things To Note

  1. Ensure that the operands in the concatenation operation are of string type or are converted to strings using functions like CStr().
  2. Be mindful of the length of the concatenated string, especially when working with large data sets. Very long strings can consume significant memory and may impact performance.
  3. Use string manipulation functions like Trim, Left, Right, or Mid to remove unnecessary spaces or characters from the concatenated result, ensuring the desired format.
  4. Consider using the StringBuilder object when concatenating a large number of strings or performing frequent concatenation operations, as it provides better performance compared to the ampersand operator.

Frequently Asked Questions (FAQs)

1. How to concatenate a string and an integer in Excel VBA?

To VBA concatenate a string and an integer, you can use the ampersand (&) operator. However, since an integer is a non-string value, you need to convert it to a string before concatenation.

2. Why is VBA concatenate not working?

If VBA concatenate is not working as expected, there could be several reasons:
1. Check that you have properly declared and assigned values to the variables involved in the VBA concatenate.
2. Verify that the operands in the concatenation operation are of string type or have been appropriately converted to strings.
3. Ensure that you have used the correct syntax for concatenation, such as the ampersand (&) operator or the “JOIN” function.
4. Double-check for any syntax errors or missing characters in your code.

3. How do I concatenate two columns in VBA?

To VBA concatenate two columns, you can use the Range object and the ampersand (&) operator. Here’s an example that concatenates the values from columns A and B into column C:

Dim lastRow As Long
Dim i As Long
lastRow = Cells(Rows.Count, “A”).End(xlUp).Row ‘ Assuming data starts from row 1
For i = 1 To lastRow
Cells(i, “C”).Value = Cells(i, “A”).Value & Cells(i, “B”).Value
Next i

In this code, the variable “lastRow” is assigned the last row number of column A with data. The loop iterates through each row from 1 to the last row and concatenates the values from columns A and B. The result is written in column C.

4. What is the opposite of concatenate in Excel VBA?

The opposite of VBA concatenate is splitting or separating a string into multiple parts. To split a string, you can use various techniques such as the Split function, string manipulation functions (like Left, Right, Mid), or regular expressions, depending on your specific requirements.

Download Template

This article must be helpful to understand VBA Concatenate, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to VBA Concatenate. Here we explain how to concatenate strings in VBA using Join function with examples & downloadable excel template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *