Excel VBA Join Function: Syntax, Examples, and Tips

VBA & macros · Updated November 21, 2026

In Excel VBA, Join combines the items in a one-dimensional array into one string, placing a chosen delimiter between them. Its syntax is Join(sourcearray, [delimiter]); if you omit the delimiter, VBA uses a space.

What the VBA Join function does

Join is a Visual Basic for Applications function, not a worksheet formula. It is useful when a macro has several text values and needs to turn them into a single string—for example, a comma-separated list, a line of labels, or a value to write to a text file.

The function does not combine worksheet cells directly. It expects a one-dimensional array as its first argument. A range containing several cells usually returns a two-dimensional array from its Value property, so you need to convert that data to a one-dimensional array before passing it to Join.

The result is a string. Join does not change the original array, add quotation marks around values, or apply special formatting to numbers and dates. If the values need a particular display format, convert or format them before joining.

Syntax and delimiter behavior

The syntax is Join(sourcearray, [delimiter]). sourcearray is the required one-dimensional array of items to combine. delimiter is optional and specifies the text inserted between adjacent items.

  • When the delimiter is omitted, the separator is a single space.
  • Use a comma and a space, such as ", ", for a readable list.
  • Use "" for no separator at all.
  • A delimiter can contain more than one character, such as " | " or vbCrLf for line breaks.

The delimiter appears between items, not before the first item or after the last. For example, joining three items with ", " produces first, second, third, not , first, second, third,.

A basic VBA Join example

This macro creates an array, joins its elements with a vertical bar, and displays the resulting string. The array returned by Array is suitable input for Join.

Sub JoinArrayExample()
    Dim parts As Variant
    Dim combined As String

    ' Create an array of text values.
    parts = Array("North", "South", "West")
    combined = Join(parts, " | ")

    MsgBox combined
End Sub

The message box displays North | South | West. Change the second argument to ", " for a comma-separated result, or leave it out to get North South West.

You can also use a declared string array when the values are known in advance. For example, assign values to Dim words(0 To 2) As String and then call Join(words, "-"). This returns the three words separated by hyphens. The array must be one-dimensional; a two-dimensional array is not a direct substitute.

Joining values from a worksheet range

For a multi-cell range, Excel returns a two-dimensional array: the first dimension represents rows and the second represents columns. The following example assumes the values are in A2:A5. It copies nonblank, non-error values into a one-dimensional string array, then joins them with commas.

Sub JoinColumnValues()
    Dim values As Variant
    Dim parts() As String
    Dim i As Long
    Dim itemCount As Long
    Dim combined As String

    ' A2:A5 is a multi-cell, single-column range.
    values = ThisWorkbook.Worksheets("Sheet1").Range("A2:A5").Value
    ReDim parts(0 To UBound(values, 1) - 1)

    For i = 1 To UBound(values, 1)
        If Not IsError(values(i, 1)) Then
            If Len(CStr(values(i, 1))) > 0 Then
                parts(itemCount) = CStr(values(i, 1))
                itemCount = itemCount + 1
            End If
        End If
    Next i

    If itemCount = 0 Then
        combined = ""
    Else
        ReDim Preserve parts(0 To itemCount - 1)
        combined = Join(parts, ", ")
    End If

    MsgBox combined
End Sub

For example, if A2:A5 contains Red, a blank cell, Blue, and Green, the result is Red, Blue, Green. The code skips cells containing Excel errors and values whose string representation has zero length. A cell containing spaces is not considered blank by this test, so trim the text first if spaces should be ignored. The code assumes A2:A5 contains multiple cells; a single-cell range has a different value shape and needs separate handling.

Choosing separators and handling different values

Pick the delimiter based on where the result will be used. A comma and space is easy to read, while a tab may suit data copied into another application. For a multi-line message, use vbCrLf between items. If the output will be parsed by another system, confirm that the delimiter cannot also appear inside an item, or use an escaping or quoting scheme appropriate to that format.

When joining numbers or dates, VBA converts each value to text. The resulting representation can depend on the value and regional settings; a date may not have the format a report requires. To control the output, explicitly convert values before assigning them to the array—for instance, use VBA’s Format function for a date or numeric value. Also decide whether empty strings and blank cells should remain as empty positions or be omitted. Join does not have an “ignore blanks” option of its own.

For example, joining Array("A", "", "C") with ", " retains the empty middle item, producing A, , C. If that is undesirable, filter empty items before calling Join, as the range example does.

Common errors and troubleshooting

Many problems come from passing data in a shape or state that Join cannot use. Check these points when a macro fails or produces unexpected text:

  • Array not initialized: A dynamic array that has not been assigned or sized does not contain elements to join. Populate or size it first.
  • Wrong dimensions: A two-dimensional range array cannot be passed directly. Flatten the relevant row or column into a one-dimensional array.
  • Cell errors: A worksheet error value such as #N/A is not ordinary text. Check with IsError and decide whether to skip it or substitute a label.
  • Unexpected spaces: An omitted delimiter inserts a space. Specify the delimiter explicitly when the required output has no separator or uses punctuation.
  • Wrong bounds: Do not assume every array starts at index zero. Use LBound and UBound when looping over an array whose lower bound is not guaranteed.

An array with zero usable items should be handled before calling Join, especially when it is built dynamically. Returning an empty string for that case makes the macro’s behavior clear. If the first argument is an array of Variants, ensure each element can be converted to text and handle error values deliberately.

Join versus the Excel TEXTJOIN function

Use VBA Join inside a macro or a VBA procedure. Use the worksheet function TEXTJOIN when the desired result belongs in a cell and should update as worksheet values change. TEXTJOIN is available in Microsoft 365 and Excel 2019 or later. Unlike VBA Join, it accepts ranges and includes an argument for ignoring empty cells.

With values in A2:A5, this worksheet formula joins them with comma-space separators and ignores empty cells:

=TEXTJOIN(", ",TRUE,A2:A5)

For example, if A2:A5 contains Red, a blank, Blue, and Green, the formula returns Red, Blue, Green. In older Excel versions without TEXTJOIN, a formula or VBA approach may be needed. A worksheet formula cannot call the built-in VBA Join function by simply typing =Join(...).

Using Join in a worksheet custom function

If you want to call joining logic from a cell, you can write a VBA user-defined function (UDF). This example accepts a range, skips blank cells and error values, and uses Join internally. Put it in a standard VBA module, not in a worksheet cell’s formula bar as code.

Public Function JoinCells(ByVal source As Range, _
                          Optional ByVal delimiter As String = ", ") As String
    Dim cell As Range
    Dim parts() As String
    Dim itemCount As Long

    ReDim parts(0 To source.Cells.Count - 1)

    For Each cell In source.Cells
        If Not IsError(cell.Value) Then
            If Len(CStr(cell.Value)) > 0 Then
                parts(itemCount) = CStr(cell.Value)
                itemCount = itemCount + 1
            End If
        End If
    Next cell

    If itemCount = 0 Then
        JoinCells = ""
        Exit Function
    End If

    ReDim Preserve parts(0 To itemCount - 1)
    JoinCells = Join(parts, delimiter)
End Function

With values in A2:A5, enter =JoinCells(A2:A5, ", ") in a worksheet cell to return a comma-separated string. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain VBA. A UDF is useful for a reusable calculation, but it is not the same as a normal worksheet function: VBA macros do not run in Excel for the web, and users may need to enable macros in the desktop app according to their organization’s security rules.

Running and saving VBA safely

On Excel for Microsoft 365 for Windows, open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > Module, paste the procedure, and run it from the editor or through Excel’s macro controls. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer.

On Excel for Mac, the Visual Basic Editor is also available, though keyboard shortcuts can differ by keyboard and system settings; Option+F11 or Fn+Option+F11 may be required. Excel for the web cannot create, edit, or run VBA macros. Before running any macro that changes workbook contents, save a copy of the workbook. Macro actions generally cannot be reversed with Ctrl+Z, so keep the original data recoverable.

Frequently Asked Questions

What is the default delimiter for VBA Join?
If you omit the delimiter argument, Join inserts a single space between array elements.

Can VBA Join combine cells directly?
No. It accepts a one-dimensional array, not a Range object. Read the range’s values and convert them to a one-dimensional array first, or use a worksheet function such as TEXTJOIN where available.

Does Join skip blank items automatically?
No. Empty elements remain in the output, so adjacent delimiters may appear. Filter blank elements before calling Join if you want to omit them.

Can I use Join as an Excel formula?
Not as a built-in worksheet formula. Use TEXTJOIN for a cell formula, or create a VBA UDF such as JoinCells and save the file in a macro-enabled format.

Why does Join fail with a range’s Value?
A multi-cell range usually produces a two-dimensional array, while Join requires one dimension. Copy the needed row or column values into a one-dimensional array, and handle empty arrays and error cells as needed.

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.