VBA Join Function: Combine Strings with a Delimiter

VBA & macros · Updated November 16, 2026

In VBA, Join combines the items in a one-dimensional array into one string, placing a delimiter between each item. For example, Join(Array("red", "green", "blue"), ", ") returns red, green, blue.

What the VBA Join function does

Join is a built-in VBA function for assembling text that is already stored as an array. It processes the array in order and inserts the chosen delimiter between neighboring elements. It does not sort the items, remove blanks, trim spaces, or read a worksheet range by itself.

This makes it useful when a macro gathers several values and needs to turn them into a line of text: a list of names, a comma-separated set of labels, a file path assembled from parts, or a message containing several selected values. The output is a single VBA string; it is not a worksheet formula and cannot be entered directly into a cell as =Join(...).

The array’s order is the output order. If its items are “red,” “green,” and “blue,” the joined string follows that sequence unless you rearrange the array first. The delimiter appears only between items, not before the first or after the last.

Syntax and delimiter behavior

The syntax is Join(sourcearray, [delimiter]). The required argument is a one-dimensional array containing the text pieces. The optional delimiter is the text placed between them. If you leave it out, VBA uses a space. Passing an empty string as the delimiter joins the pieces with nothing between them.

  • Join(parts, ", ") separates elements with a comma and a space.
  • Join(parts, "|") separates elements with a vertical bar.
  • Join(parts, vbCrLf) puts a Windows line break between elements in a text string.
  • Join(parts, vbNullString) concatenates the elements without a separator.

For a line break inside an Excel worksheet cell, use vbLf as the delimiter; Excel cells use a line-feed character for an in-cell line break. Turn on Wrap Text if you want the separate lines to display. A delimiter is ordinary text, so it can be more than one character, such as " - " or " and ".

A simple VBA example

VBA’s Array function is a convenient way to create a small array for a quick operation. Assigning it to a Variant is a common pattern because Array returns a Variant containing an array.

Sub ShowJoinedWords()
    Dim words As Variant
    Dim result As String

    ' Create an array, then join its elements with a comma and a space.
    words = Array("North", "East", "West")
    result = Join(words, ", ")

    MsgBox result
End Sub

The message box displays North, East, West. Change the delimiter to " / " and the result becomes North / East / West. If the delimiter is omitted, the result is North East West. A one-item array simply returns that item, because there is no pair of elements to separate.

To try the example in Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the procedure into the module. Run it with the cursor inside the procedure by pressing F5. On Mac, open the editor using Developer > Visual Basic; Option+F11 may also work, depending on the keyboard and function-key settings.

Joining values from an Excel range

A frequent point of confusion is that a multi-cell range’s .Value is normally a two-dimensional array: one dimension for rows and another for columns. Join expects a one-dimensional array, so passing the value of a multi-cell range directly to it is not the right approach. Copy the range’s values into a one-dimensional string array first.

The following macro assumes that A1:A3 contains the source values and that the worksheet is named Sheet1. It writes the joined result to B1.

Sub JoinCellsInColumn()
    Dim ws As Worksheet
    Dim source As Variant
    Dim parts() As String
    Dim i As Long

    ' Read three worksheet cells into a two-dimensional array.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    source = ws.Range("A1:A3").Value

    ' Copy the values into a zero-based, one-dimensional string array.
    ReDim parts(0 To UBound(source, 1) - 1)
    For i = 1 To UBound(source, 1)
        parts(i - 1) = CStr(source(i, 1))
    Next i

    ' Join the array and place the result in B1.
    ws.Range("B1").Value = Join(parts, ", ")
End Sub

If A1:A3 contain Oak, Pine, and Maple, the macro writes Oak, Pine, Maple to B1. The code is deliberately set up for a three-cell range; if you change the range, the loop uses the number of rows returned. A single-cell range can return a scalar rather than a two-dimensional array, so code intended to handle either one cell or many should test the range size and handle the single-cell case separately.

Blank cells, data types, and ordering

Join does not treat an empty string as an instruction to skip an element. For example, joining Array("Oak", "", "Maple") with ", " leaves an empty position between the separators, producing Oak, , Maple. Decide whether that is acceptable before building the array. If blanks should be excluded, filter them out while copying values into the array rather than expecting Join to remove them.

The range example uses CStr to convert each cell value to text. That is useful when cells contain numbers or dates, but the displayed result follows VBA’s conversion rules, not necessarily the cell’s number format. For example, a date stored in a cell may become a date string without the exact appearance used by the worksheet. If the output must match a particular format, format each value explicitly before adding it to the array.

Also consider cells containing Excel errors such as #N/A. Converting an error value with CStr can cause a type mismatch. Check for error values with VBA’s IsError before conversion, and choose whether to skip them, replace them with a label, or stop with a clear message. This matters especially when the macro reads user-edited data.

Join compared with TEXTJOIN, Split, and loops

Use VBA Join when your code already has a one-dimensional array and needs a string. Use the worksheet function TEXTJOIN when the task belongs in a cell formula and should respond to worksheet values. For example, if A1:A3 contain Oak, Pine, and Maple, this formula returns Oak, Pine, Maple:

With the values in A1:A3, enter =TEXTJOIN(", ",TRUE,A1:A3) in another cell. The second argument, TRUE, tells the worksheet function to ignore empty cells. TEXTJOIN is available in Excel 2019 and later, including Microsoft 365. Unlike VBA Join, it can work directly with a range and offers an option to ignore empty values.

Split performs a related but opposite task: it breaks one string into an array at a chosen delimiter. For example, Split("Oak|Pine|Maple", "|") produces separate elements that can later be passed to Join. A round trip preserves the original pieces only if the delimiter does not also occur inside a piece, or if your code has a separate escaping rule. Join does not quote or escape delimiter characters automatically.

A loop that appends text can be easier when each item needs custom handling, such as conditional labels or special formatting. For a straightforward array-to-string operation, Join is shorter and makes the separator behavior clear. For a worksheet formula, a macro is usually unnecessary if TEXTJOIN already meets the requirement.

Common problems and safe macro use

  • Type mismatch: Check that the input is a one-dimensional array and that its elements can be converted to strings. A two-dimensional range array is a common cause.
  • Unexpected spaces: An omitted delimiter inserts spaces. Specify the delimiter explicitly when the output needs commas, line breaks, or no separator.
  • Extra separators: Empty elements are retained. Remove or replace them before joining if blank entries should not appear.
  • Wrong order: Join keeps the array’s existing order. Sort or rearrange the elements first if a different order is needed.
  • Unexpected cell display: Converting a cell value to text may not preserve its number format. Format values deliberately when exact display matters.

Before running a macro that writes to a workbook, save a separate copy. VBA macros cannot be undone with Ctrl+Z (or Command+Z on Mac), so a backup is the practical way to recover from an unwanted change. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain the code. Run only macros you understand and trust.

Frequently Asked Questions

Can I use Join directly on a worksheet range?
No, not reliably for a multi-cell range: its value is a two-dimensional array, while Join expects a one-dimensional array. Copy the range values into a one-dimensional array first, or use the worksheet function TEXTJOIN if a formula is suitable.

What happens if I omit the delimiter in VBA Join?
VBA uses a space between elements when the optional delimiter is omitted. To join without spaces, pass an empty string explicitly; to use another separator, pass that text as the second argument.

Does Join skip blank array elements?
No. Blank elements remain in the sequence, so their neighboring delimiters can appear next to each other. Filter or skip blanks before calling Join if you do not want empty positions.

Can Join combine numbers?
It is intended for arrays of strings. Convert numeric values to strings explicitly when needed, and format them first if the output must show a particular number of decimal places, date format, or other presentation.

Is VBA Join the same as Excel TEXTJOIN?
No. Both combine text with a delimiter, but VBA Join takes a one-dimensional array, while worksheet TEXTJOIN can work with ranges and can be told to ignore empty cells. Choose based on whether the operation belongs in a macro or a worksheet formula.

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.