Excel VBA Concatenate: How to Join Strings in Macros

VBA & macros · Updated November 22, 2026

The direct answer: in Excel VBA, you concatenate strings with the ampersand operator (&), for example Range("A1") = "Hello " & "World". You can also use the Concatenate worksheet function inside VBA via Application.WorksheetFunction.Concatenate, but & is faster, simpler, and the standard choice for VBA code. This article explains both methods, how to handle numbers and dates, common errors, and practical examples you can adapt.

Why Use & Instead of the Concatenate Function in VBA

In Excel worksheets, you have the CONCATENATE function and its modern replacement CONCAT. In VBA, the ampersand operator is the native way to join text. It is a true operator, so it works in any expression, not just inside a function call. The Concatenate worksheet function can be called from VBA, but it requires the Application.WorksheetFunction prefix and does not accept range references the way it does in a cell—you must pass individual arguments. For example, Application.WorksheetFunction.Concatenate("A", "B") works, but it is verbose. The & operator also handles numbers and dates automatically by converting them to their text representation, which is usually what you want.

Basic Concatenation with the Ampersand Operator

The ampersand operator joins two or more expressions into a single string. Here is a simple macro that combines first and last names:

Sub ConcatenateNames()
    Dim firstName As String
    Dim lastName As String
    Dim fullName As String
    
    firstName = Range("A1").Value
    lastName = Range("B1").Value
    
    fullName = firstName & " " & lastName
    Range("C1").Value = fullName
End Sub

Notice the space inside the quotes: " ". That is how you add a space between values. You can add any literal text, punctuation, or line breaks (using vbCrLf for a new line). The result in C1 would be, for example, “John Smith”.

Using the Concatenate Worksheet Function in VBA

If you prefer the function style, you can call the worksheet function from VBA. This is useful when you want to mirror a formula that already exists in a cell, but it is rarely necessary. Here is an example:

Sub UseConcatenateFunction()
    Dim result As String
    ' Joins the values in A1, B1, and C1 with no separators
    result = Application.WorksheetFunction.Concatenate(Range("A1").Value, Range("B1").Value, Range("C1").Value)
    Range("D1").Value = result
End Sub

Note that Concatenate does not insert separators; you must add them as extra arguments, like Application.WorksheetFunction.Concatenate(Range("A1").Value, " ", Range("B1").Value). The newer CONCAT function is not available as a worksheet function in VBA’s WorksheetFunction object in older versions, but you can use Application.Evaluate("CONCAT(...)") if needed. In practice, the ampersand is more straightforward.

Concatenating Numbers, Dates, and Booleans

When you use &, VBA converts the right operand to a string using the current locale settings. For example, a date might appear as “1/15/2024” or “15/01/2024” depending on your system. If you need a specific format, use the Format function:

Sub ConcatenateWithFormat()
    Dim price As Double
    Dim saleDate As Date
    Dim message As String
    
    price = 19.99
    saleDate = DateSerial(2024, 1, 15)
    
    message = "Price: " & Format(price, "$#,##0.00") & " on " & Format(saleDate, "yyyy-mm-dd")
    Range("A1").Value = message
    ' Result: Price: $19.99 on 2024-01-15
End Sub

For numbers, you can also use Str() to convert, but it adds a leading space for positive numbers, so Format is safer. Booleans convert to “True” or “False” automatically.

Concatenating Ranges and Arrays

To join values from multiple cells in a range, you can loop through the range and build a string. This is common for creating a comma-separated list. Here is an example that joins all non-empty cells in column A:

Sub ConcatenateRange()
    Dim cell As Range
    Dim output As String
    
    For Each cell In Range("A1:A10")
        If cell.Value <> "" Then
            If output = "" Then
                output = cell.Value
            Else
                output = output & ", " & cell.Value
            End If
        End If
    Next cell
    
    Range("B1").Value = output
End Sub

This avoids adding a trailing comma. For a large range (thousands of cells), consider reading the values into an array first for speed, but for typical use a loop is fine.

Common Errors and How to Avoid Them

One frequent mistake is using the plus sign (+) instead of &. The plus sign works for numeric addition, but when used with strings it can cause a Type Mismatch error if one operand is a number. Always use & for concatenation. Another error is forgetting to declare variables or not converting numbers before concatenation—though & handles that automatically. A third issue is the ByRef argument type mismatch when passing a range to a function that expects a string; always use .Value or .Text to extract the string. Finally, when building long strings, watch out for line breaks in the VBA editor—you can use the underscore _ to continue a line, but ensure there is a space before it.

Concatenation with Line Breaks and Special Characters

To insert a new line in a cell, use vbCrLf (carriage return + line feed) or vbLf (line feed only). For example:

Sub MultiLineMessage()
    Dim msg As String
    msg = "Line 1" & vbCrLf & "Line 2"
    Range("A1").Value = msg
    ' Remember to set Wrap Text on the cell to see both lines
End Sub

Special characters like quotes need doubling: to include a double quote in a string, use two quotes in a row. For example, "He said ""Hello""" produces He said "Hello". Tabs can be inserted with vbTab.

Performance Tips for Large Data

If you concatenate thousands of values in a loop, the repeated string concatenation can slow down your macro. A more efficient approach is to use the Join function with an array. First, load the range into an array, then use Join with a delimiter:

Sub FastConcatenate()
    Dim arr As Variant
    Dim i As Long
    Dim output As String
    
    arr = Range("A1:A10000").Value
    ReDim values(1 To UBound(arr, 1)) As String
    For i = 1 To UBound(arr, 1)
        values(i) = CStr(arr(i, 1))
    Next i
    
    output = Join(values, ",")
    Range("B1").Value = output
End Sub

This is much faster because it avoids repeated memory reallocations. For even larger data, consider writing to a text file or using the StringBuilder class, but Join is usually sufficient.

Frequently Asked Questions

Can I use the CONCATENATE function directly in VBA? Yes, you can call Application.WorksheetFunction.Concatenate, but the ampersand operator is more idiomatic and faster. The function is mainly useful when you want to mimic a worksheet formula.

How do I concatenate a range of cells with a separator in VBA? Use a loop to build the string, as shown above, or load the range into an array and use Join for speed. The loop gives you more control over skipping blanks.

Why does my concatenation show a date as a number? When you concatenate a date with &, VBA converts it to the underlying serial number if you don’t use Format. Always use Format(date, "mm/dd/yyyy") to display a readable date.

What is the difference between & and + in VBA? The & operator always concatenates, converting numbers to strings. The + operator tries to add numbers; if you use it with strings, it may cause a type mismatch error. Always use & for joining text.

Can I concatenate with a line break in a cell? Yes, use vbCrLf or vbLf in your string, and ensure the cell has Wrap Text enabled to see the new line.

How do I avoid the “Type Mismatch” error when concatenating? Ensure you are using & and that you have converted any objects (like Range) to their values with .Value. Also, declare variables with explicit types to catch issues early.

Remember to save a copy of your workbook before running any macro, because VBA actions cannot be undone with Ctrl+Z.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.