```html

VBA Concatenation Operator

Learn how to combine text, numbers, variables and cell values using the VBA concatenation operator (&).

Introduction to the VBA Concatenation Operator

In VBA, concatenation means joining two or more pieces of text together to create a single text value.

VBA uses the ampersand (&) operator for concatenation.

Simple Example:

"Hello " & "Chirag"

Result:
Hello Chirag

Concatenation is extremely useful when creating dynamic messages, reports, invoices, emails, file paths, worksheet values and user-friendly output in Excel VBA.

What is Concatenation?

Concatenation is the process of combining multiple text values into one text string.

Think of it as joining separate pieces of information together.

Example:

"Excel" + " " + "VBA"

In VBA, we use:

"Excel" & " " & "VBA"

Result: Excel VBA

VBA Concatenation Operator (&)

The ampersand symbol & is the primary concatenation operator in VBA.

Operator	Name	Purpose
&	Concatenation Operator	Joins two or more values into a text string

Important:

In VBA, use & when your intention is to join text values.

Basic Syntax

The basic syntax for concatenation is:

Result = Value1 & Value2

You can also concatenate more than two values:

Result = Value1 & Value2 & Value3

You can continue using the & operator to join many values together.

Example 1: Joining Two Text Values

Sub Example1() Dim FirstName As String Dim LastName As String Dim FullName As String FirstName = "Chirag" LastName = "Coder" FullName = FirstName & LastName MsgBox FullName End Sub

The output will be:

ChiragCoder

Notice that there is no space between the first and last name. This is because VBA only joins exactly what you provide.

Example 2: Adding a Space Between Text

If you want a space between two values, you must explicitly provide a space using " ".

Sub Example2() Dim FirstName As String Dim LastName As String Dim FullName As String FirstName = "Chirag" LastName = "Coder" FullName = FirstName & " " & LastName MsgBox FullName End Sub

Output:

Chirag Coder

Here, " " represents one blank space.

Example 3: Joining Multiple Values

You can concatenate several values in a single expression.

Dim FirstName As String Dim MiddleName As String Dim LastName As String Dim FullName As String FirstName = "Chirag" MiddleName = "Kumar" LastName = "Coder" FullName = FirstName & " " & MiddleName & " " & LastName MsgBox FullName

Output:

Chirag Kumar Coder

Example 4: Combining Text and Numbers

The concatenation operator can also be used to combine text with numeric values.

Dim Name As String Dim Age As Integer Dim Message As String Name = "Chirag" Age = 42 Message = "My name is " & Name & " and my age is " & Age MsgBox Message

Output:

My name is Chirag and my age is 42

VBA converts the numeric value into text as part of the concatenated result.

Concatenating Variables

Variables are frequently used with concatenation.

Dim Product As String Dim Quantity As Integer Dim Price As Double Product = "Laptop" Quantity = 2 Price = 50000 MsgBox "Product: " & Product & _ vbCrLf & "Quantity: " & Quantity & _ vbCrLf & "Price: " & Price

The underscore _ allows the VBA statement to continue on the next line.

Concatenating Excel Cell Values

One of the most useful applications of concatenation is combining values from different Excel cells.

Suppose your worksheet contains:

Cell	Value
A2	Chirag
B2	Coder

You can combine them using:

Range("C2").Value = Range("A2").Value & " " & Range("B2").Value

Cell C2 will contain:

Chirag Coder

Practical Example: Employee Information

Suppose an Excel sheet contains employee information:

Cell	Value
A2	101
B2	Rahul
C2	Finance

We can create a complete employee message:

Dim Message As String Message = "Employee ID: " & Range("A2").Value & _ ", Name: " & Range("B2").Value & _ ", Department: " & Range("C2").Value MsgBox Message

Output:

Employee ID: 101, Name: Rahul, Department: Finance

Using vbCrLf with Concatenation

vbCrLf is commonly used to insert a new line into a message.

Dim Message As String Message = "Name: Chirag" & vbCrLf & _ "Age: 42" & vbCrLf & _ "City: Delhi" MsgBox Message

Output:

Name: Chirag
Age: 42
City: Delhi

Creating Dynamic Messages

Concatenation becomes particularly useful when creating dynamic messages.

Dim Customer As String Dim Amount As Double Customer = "Amit" Amount = 12500 MsgBox "Dear " & Customer & _ ", your invoice amount is ₹" & Amount

Example Output:

Dear Amit, your invoice amount is ₹12500

Creating File Paths Using Concatenation

Concatenation can be used to dynamically create file paths.

Dim FolderPath As String Dim FileName As String Dim FullPath As String FolderPath = "C:\Reports\" FileName = "SalesReport.xlsx" FullPath = FolderPath & FileName MsgBox FullPath

Result:

C:\Reports\SalesReport.xlsx

Important:

When creating Windows file paths, remember to include the required backslash \ between the folder path and file name.

Concatenation in Email Automation

Concatenation is extremely useful when creating personalized email messages using VBA.

Dim CustomerName As String Dim InvoiceNo As String Dim Amount As Double Dim EmailBody As String CustomerName = "Rahul" InvoiceNo = "INV1001" Amount = 25000 EmailBody = "Dear " & CustomerName & "," & vbCrLf & vbCrLf & _ "Your invoice number is " & InvoiceNo & "." & vbCrLf & _ "The total amount is ₹" & Amount & "." & vbCrLf & vbCrLf & _ "Regards," & vbCrLf & _ "ChiragCoder" MsgBox EmailBody

This technique can be used to create personalized email content automatically from Excel data.

Concatenation vs Addition

A very important concept is understanding the difference between the & operator and the + operator.

Operator	Purpose	Example	Result
+	Mathematical addition	10 + 20	30
&	Text concatenation	"10" & "20"	1020

Dim Result As String Result = "10" & "20" MsgBox Result

The result is: 1020

Why Prefer & for Concatenation?

VBA can sometimes use the + operator with strings, but it is better practice to use & when your intention is specifically to concatenate text.

Recommended:

FullName = FirstName & " " & LastName

The ampersand clearly communicates that the values should be joined rather than mathematically added.

Concatenating Empty Values

Sometimes a variable or Excel cell may contain an empty value. You should understand how concatenation behaves in these situations.

Dim FirstName As String Dim LastName As String FirstName = "" LastName = "Coder" MsgBox FirstName & " " & LastName

The result will be:

Coder

Using Trim with Concatenation

Extra spaces can sometimes appear when combining values. The Trim() function can help remove unnecessary spaces from the beginning and end of text.

Dim FirstName As String Dim LastName As String Dim FullName As String FirstName = " Chirag " LastName = " Coder " FullName = Trim(FirstName) & " " & Trim(LastName) MsgBox FullName

Result:

Chirag Coder

Concatenating Numbers

Numeric values can also be combined with text.

Dim Quantity As Integer Dim Price As Double Quantity = 5 Price = 100 MsgBox "Quantity = " & Quantity & _ ", Price = " & Price

Output:

Quantity = 5, Price = 100

Using Format with Concatenation

When displaying currency, dates or numbers, it is often useful to format the value before concatenating it.

Dim Amount As Double Amount = 125000.5 MsgBox "Total Amount: " & Format(Amount, "#,##0.00")

Output:

Total Amount: 125,000.50

Concatenating Dates

Dates can be converted into a readable format before they are joined with other text.

Dim InvoiceDate As Date InvoiceDate = Date MsgBox "Invoice Date: " & Format(InvoiceDate, "dd-mm-yyyy")

This is useful for reports, invoices, emails and automated documents.

Common Mistakes

1. Forgetting the & Operator

FullName = FirstName LastName

This is invalid because VBA needs an operator between values.

2. Forgetting Spaces

FullName = FirstName & LastName

This produces: ChiragCoder

Use:

FullName = FirstName & " " & LastName

3. Using + Instead of &

For text concatenation, prefer the ampersand operator.

4. Forgetting Quotation Marks

Message = Hello & Name

If Hello is literal text, it must be inside quotation marks.

Message = "Hello " & Name

Practical Project: Create Customer Message

Let's create a complete customer message using Excel cell values.

Suppose:

Cell	Value
A2	1001
B2	Amit Sharma
C2	25000

Sub CustomerMessage() Dim CustomerID As String Dim CustomerName As String Dim Amount As Double Dim Message As String CustomerID = Range("A2").Value CustomerName = Range("B2").Value Amount = Range("C2").Value Message = "Customer ID: " & CustomerID & vbCrLf & _ "Customer Name: " & CustomerName & vbCrLf & _ "Invoice Amount: ₹" & Format(Amount, "#,##0.00") MsgBox Message End Sub

Output:

Customer ID: 1001
Customer Name: Amit Sharma
Invoice Amount: ₹25,000.00

VBA Concatenation Quick Reference

Expression	Result
"Hello" & "World"	HelloWorld
"Hello " & "World"	Hello World
"Age: " & 42	Age: 42
"₹" & 5000	₹5000
"A" & "B" & "C"	ABC
Range("A1") & Range("B1")	Combines A1 and B1

Practice Questions

Question 1:

Write VBA code to combine FirstName and LastName with a space between them.

Answer:

FirstName & " " & LastName

Question 2:

What is the output of:

"Excel" & " " & "VBA"

Answer: Excel VBA

Question 3:

How do you combine text and a variable named Age?

Answer:

"Age is " & Age

Question 4:

Which operator is normally used for text concatenation in VBA?

Answer: &

Question 5:

Write VBA code to combine values from A2 and B2 into C2 with a space between them.

Answer:

Range("C2").Value = Range("A2").Value & " " & Range("B2").Value

VBA Operators Learning Path

Continue learning VBA operators through the following lessons:

Key Takeaway:

The VBA concatenation operator & is used to join text, variables, numbers, cell values and other expressions into one text result.

Remember:

Text1 & Text2

Use:
" " when you need to insert a space between values.

```

Excel VBA Concatenation Operator (&) – Complete Guide with Examples