In Excel VBA, use Join(array, delimiter) to combine the elements of a one-dimensional array into a single string. For example, Join(Array("red", "green", "blue"), ", ") returns red, green, blue; a worksheet range usually needs extra handling because its values are commonly a two-dimensional array.
What VBA Join does
Join is a built-in VBA function that takes an array of text items and places a chosen separator between consecutive items. It returns one string; it does not change the original array or write anything to a worksheet by itself. This makes it useful for creating comma-separated labels, file-name components, messages, or text to pass to another procedure.
The general syntax is Join(sourcearray, [delimiter]). The source must be an array, and it must be one-dimensional. The delimiter is optional: if you leave it out, VBA uses a space. If you provide it, VBA inserts that exact text between elements, not before the first element or after the last.
Sub ShowJoinSyntax()
Dim items As Variant
Dim result As String
' Array creates an array of text values.
items = Array("North", "East", "West")
result = Join(items, " | ")
Debug.Print result ' North | East | West
End Sub
In the VBA editor, Debug.Print sends the result to the Immediate window, which you can show with Ctrl+G on Windows. The code only prints a value; it does not place it in a cell. Before running any macro, save a copy of the workbook: changes made by VBA cannot be undone with Ctrl+Z.
Basic array examples
For a quick one-off combination, VBA’s Array function is convenient. It produces a Variant containing an array, so you can pass it directly to Join or assign it to a Variant variable first. The following example produces Excel VBA makes strings.
Sub JoinWords()
Dim words As Variant
Dim sentence As String
' Keep each word as a separate array element.
words = Array("Excel", "VBA", "makes", "strings")
sentence = Join(words, " ")
Debug.Print sentence
End Sub
For reusable code, declare a string array instead. This makes the intended data type clear and can help avoid surprises when values come from calculations or worksheet cells. A string array also gives you control over the bounds when you dimension it yourself.
Sub JoinStringArray()
Dim colors(0 To 2) As String
Dim result As String
' Fill the one-dimensional string array.
colors(0) = "amber"
colors(1) = "blue"
colors(2) = "violet"
result = Join(colors, "; ")
Debug.Print result ' amber; blue; violet
End Sub
VBA array bounds are not something to assume in every project: declarations can use different bounds, and an Option Base statement can affect some arrays created with Array. Join takes care of iterating the array, so you do not need to write an index loop just to concatenate it.
Choosing and formatting the delimiter
The delimiter can be any string, including multiple characters. Use ", " for readable comma-separated text, vbCrLf for line breaks, or a tab character when preparing tab-delimited text. The delimiter is literal: Join(items, "and") produces text such as teaandcoffee, not a grammatically formatted list.
- Comma and space: readable lists, such as
apples, pears, plums. - Hyphen: compact identifiers, such as
2026-11-03when the elements are already formatted as text. - Line break: separate lines in a message or cell. Excel cells may need Wrap Text enabled to show multiple lines clearly.
- Empty string: concatenate items with no separator. This can be useful for assembling a string, but adjacent values may be hard to distinguish.
When the final text is meant to be parsed by another system, choose a delimiter that cannot be confused with the data, or escape/quote the data according to that system’s rules. Joining fields with commas alone does not create a complete CSV writer: values containing commas, quotation marks, or line breaks need CSV-specific quoting.
Joining values from an Excel range
A common misconception is that a range can be passed directly to Join. It cannot: Join expects a one-dimensional array, while the value returned by a multi-cell range is normally a two-dimensional Variant array. A single-cell range is different again: its Value2 is a scalar rather than an array. Flatten the cells into a one-dimensional string array before calling Join.
This worksheet function accepts a contiguous range, skips blank cells and cells containing Excel errors, converts the remaining values to text, and joins them in cell order. Put it in a standard VBA module. In a worksheet, use a formula such as =JoinRangeText(A1:A5, ", "). The example assumes the range contains ordinary text or values that can be converted to text.
Public Function JoinRangeText(ByVal source As Range, ByVal delimiter As String) As String
Dim parts() As String
Dim cellValue As Variant
Dim cellIndex As Long
Dim used As Long
' Allocate enough room for every cell; blanks and errors are skipped.
ReDim parts(0 To source.Cells.Count - 1)
For cellIndex = 1 To source.Cells.Count
cellValue = source.Cells(cellIndex).Value2
If Not IsError(cellValue) Then
If Len(CStr(cellValue)) > 0 Then
parts(used) = CStr(cellValue)
used = used + 1
End If
End If
Next cellIndex
' The default return value is an empty string if nothing was included.
If used = 0 Then Exit Function
ReDim Preserve parts(0 To used - 1)
JoinRangeText = Join(parts, delimiter)
End Function
For example, if A1:A5 contains Red, a blank, Green, an Excel error, and Blue, the formula returns Red, Green, Blue. This function deliberately skips errors rather than displaying their error values. It also skips truly empty cells and formulas whose displayed result is an empty string. A cell containing spaces is not empty to this code, so trim values first if spaces should be ignored.
Custom worksheet functions require the workbook to retain its VBA project, typically by saving as an Excel Macro-Enabled Workbook (.xlsm). Users may need to enable macros for a trusted file. The function reads the supplied cells and returns text; it does not write into the range. If you need to preserve numbers in a particular format, format or convert them deliberately before joining: Value2 supplies the underlying value, and CStr can render numbers or dates according to regional settings rather than the cell’s displayed number format.
Two-dimensional arrays and other data types
Arrays returned by Range.Value or Range.Value2 for multiple cells have row and column dimensions. Passing that array directly to Join does not flatten it; a two-dimensional array does not meet the function’s one-dimensional requirement. For a single row or column, loop through its cells and copy the values into a one-dimensional array, as the range function above does.
For a rectangular block with multiple rows and columns, decide the order you want before flattening: row by row, column by column, or separate joined strings for each row. The order matters because the output is just a sequence of text. A worksheet range also may contain numbers, dates, Boolean values, blanks, and errors, not only text. Convert and handle these explicitly if the result needs consistent formatting or if errors must be reported rather than omitted.
If the array contains numeric or other non-string values, do not rely on implicit conversion for a polished output. Convert each value to a string using the format your application requires, then join the resulting strings. For example, a date may otherwise become a locale-dependent string. Also decide whether empty elements should remain: joining Array("A", "", "C") with a comma and space leaves two separators with an empty position between them. Skipping empty values changes that behavior and may not be appropriate when positions carry meaning.
Common errors and how to fix them
When Join fails or the result looks wrong, check the input shape and contents before changing the delimiter. These are the most useful checks:
- Type mismatch: confirm the argument is an array, not a scalar cell value or a Range object. A one-cell range needs to be handled as one value; a multi-cell range needs flattening.
- Wrong number of dimensions: convert a two-dimensional range array into a one-dimensional array first. Do not expect
Jointo choose an order for a table. - Unexpected separators: blank elements are still elements in the array. Filter them out if you want to omit their positions.
- Unexpected number or date text: explicitly format or convert values before joining. General string conversion may not match the cell’s displayed number format.
- Array bounds problems in your own loop: use
LBoundandUBoundwhen looping through an array whose bounds are not known. An index that assumes the array starts at zero can fail. - Worksheet function shows a name error: check that the function is in a standard module, the workbook is macro-enabled, and macros are allowed for that trusted workbook.
Test edge cases as well as the normal list: one item, no nonblank items, blanks at the beginning or end, and cells with errors. A one-element array joined with a delimiter returns that element without adding the delimiter. For an empty result, decide whether an empty string is acceptable or whether the calling code should display a message such as “No items.”
Join compared with alternatives
Use Join when your data is already in a one-dimensional array and you want a string result. It is shorter and clearer than building a string one element at a time. A loop is still useful when each element needs special formatting, conditional inclusion, or a different separator. In that case, the loop can construct the array first or append formatted pieces while tracking whether an item is the first one.
For a worksheet-only solution, Excel’s TEXTJOIN function can combine cells and optionally ignore empty cells, without VBA. It is available in Microsoft 365 and newer Excel versions, including Excel 2019 and Excel 2021. It is often the simpler choice if the task is just to combine a range in a cell and the function is available in the target workbook. VBA Join is more appropriate when an existing macro already works with arrays or when the result belongs in code rather than a worksheet formula.
On Excel for Mac, VBA’s Join function and the array logic are essentially the same. The VBA editor and macro security controls may look different from Windows, but the function syntax does not change. Excel for the web can open a macro-enabled workbook but does not run VBA macros; use desktop Excel to execute the VBA or use a formula-based approach supported in the web app.
Frequently Asked Questions
What is the VBA syntax for joining an array?
Use Join(sourcearray, delimiter), such as Join(items, ", "). The source must be a one-dimensional array; the delimiter is optional and defaults to a space.
Can I use Join directly on a cell range?
No. A Range object is not the one-dimensional array Join requires. Read the range values, flatten them into a one-dimensional array, and then call Join.
How do I join an array with a line break?
Pass vbCrLf as the delimiter, for example Join(items, vbCrLf). When the result is shown in an Excel cell, enable Wrap Text if you want the line breaks to be visible as separate lines.
Does Join ignore blank array elements?
No. A blank element remains in the sequence, so it can produce adjacent delimiters. Filter blank elements before joining if you want to omit them; do not filter them if their positions are meaningful.
Can Join combine a two-dimensional array?
No. A two-dimensional array must first be flattened into a one-dimensional array. For worksheet ranges, choose a consistent cell order and decide how to handle blank, error, numeric, and date values.
Related
- Excel Text to Columns: Split at the First Delimiter
- Excel Text to Columns: Use a Line Break Delimiter
- Excel Text to Columns: Split at the Last Space
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- How to Create a Word Template With MacrosTo create a Word template that contains a macro, start with a blank document or an existing file,…
- Microsoft Office Macro Recorder: How It WorksMicrosoft Office has macro recorders in desktop Excel and Word; in Excel for Microsoft 365 on Windows, choose…
- VBA Dictionary Early Binding: Setup, Benefits, and LimitsYes. To use a VBA Dictionary with early binding in Excel for Microsoft 365 on Windows, add a…
- Excel VBA: Hide a Range of Cells or RowsIn Excel VBA, you can hide the entire rows or columns that contain a range, but you cannot…
- VBA Split Function: How to Split Text into an ArrayIn VBA, use Split(text, delimiter) to break a string wherever a chosen delimiter appears; the result is an…
- VBA Array Size: Practical Limits, Bounds, and Memory UseThere is no single fixed maximum size for a VBA array: its practical limit depends on available memory,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.